Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Remove weekends in the Date Diff function 1
- Thread starter Syerston
- Start date
joebickley
Programmer
this is a huge pain and im ammazed microsoft hasnt sorted out a function to do this. I have sorted a method to do it but it is a little crude.
This method tales away 2 days for every 7 days that have passed. This isnt entirely acurate but depends on how strict u need it to be.
(datediff(dateA,dateB)- (cint(datediff(dateA,dateB)/7)*2))
The only other way i know you can do it is to put a string together that counts up all the mondays between the dates and then adds all the tuesday weds etc etc
Hope this helps
This method tales away 2 days for every 7 days that have passed. This isnt entirely acurate but depends on how strict u need it to be.
(datediff(dateA,dateB)- (cint(datediff(dateA,dateB)/7)*2))
The only other way i know you can do it is to put a string together that counts up all the mondays between the dates and then adds all the tuesday weds etc etc
Hope this helps
This has been done in the MS Access Handbook
here is the code I use
Function OpenworkDays(OpenDate, Optional CloseDate) As Integer
Dim OpDate As Date
Dim ClDate As Date
Dim i As Date 'counter
Dim WrkDays As Integer
If Not IsDate(OpenDate) Then
MsgBox "Valid opendate not supplied"
Exit Function
Else
OpDate = CVDate(OpenDate)
End If
If (IsMissing(CloseDate) Or Not IsDate(CloseDate)) Then CloseDate = Date
ClDate = CVDate(CloseDate)
WrkDays = 0 'initialize for working days
For i = OpDate To ClDate
If WeekDay(i) <> 1 And WeekDay(i) <> 7 Then
WrkDays = WrkDays + 1
End If
Next
OpenworkDays = WrkDays
End Function
Please note this function will break on nulls so you may have to use NZ or another work around.
here is the code I use
Function OpenworkDays(OpenDate, Optional CloseDate) As Integer
Dim OpDate As Date
Dim ClDate As Date
Dim i As Date 'counter
Dim WrkDays As Integer
If Not IsDate(OpenDate) Then
MsgBox "Valid opendate not supplied"
Exit Function
Else
OpDate = CVDate(OpenDate)
End If
If (IsMissing(CloseDate) Or Not IsDate(CloseDate)) Then CloseDate = Date
ClDate = CVDate(CloseDate)
WrkDays = 0 'initialize for working days
For i = OpDate To ClDate
If WeekDay(i) <> 1 And WeekDay(i) <> 7 Then
WrkDays = WrkDays + 1
End If
Next
OpenworkDays = WrkDays
End Function
Please note this function will break on nulls so you may have to use NZ or another work around.
-
1
- #4
MichaelRed
Programmer
For all, come see the fab faq181-291 and be overjoyied at the muse of simplicity. All the above just cause more trouble, as no sooner than you do the half-hacked ver than the POWERS THAT BE will be back with the HOLIDAYS - as the next question!
Why not do it all?
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Why not do it all?
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
Programmer
Hmmmmmmmmmmmmmmmmm,
well, at least ONE of us has, try faq181-261 perhaps I got my head (and eyes) on straight this morning?
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
well, at least ONE of us has, try faq181-261 perhaps I got my head (and eyes) on straight this morning?
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
- Status
Similar threads
- Locked
- Question
