Dates and Business days

[vbcraft]

Hi,
Here is what i am trying to do:
I want to take todays date and return the appropriate day which is 50 days
from today. I want to use business days only.
ie. Now() - 50
but only using days monday to friday
Does anyone know how to do this

 

[Tom van Stiphout]

On Thu, 16 Jul 2009 06:09:01 -0700, vbcraft

Sal Ricciardi wrote a nice article about this, here:
http://msdn.microsoft.com/en-us/library/dd327646.aspx

Btw, Now()-50 is a bad idea. Use DateAdd for date math.

-Tom.
Microsoft Access MVP

 

[Klatuu]

Here is a function that does exactly that. To exclude week ends is easy, but
to capture holidays, you need a holiday table that has a record for each date
that is not a work day. This code includes such a table. If you have a
holiday table, change the code to use your names; if not, create a table.
Mine has only two fields - a date and a description.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Douglas J. Steele]

Tsk, tsk, tsk. Not everyone has mm/dd/yyyy set as their Short Date format!
<g>

CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is a function that does exactly that. To exclude week ends is easy,
but
to capture holidays, you need a holiday table that has a record for each
date
that is not a work day. This code includes such a table. If you have a
holiday table, change the code to use your names; if not, create a table.
Mine has only two fields - a date and a description.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding
Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Hi,
Here is what i am trying to do:
I want to take todays date and return the appropriate day which is 50
days
from today. I want to use business days only.
ie. Now() - 50
but only using days monday to friday
Does anyone know how to do this

 

[Klatuu]

wel, they should! What's the matter with them
--
Dave Hargis, Microsoft Access MVP

Tsk, tsk, tsk. Not everyone has mm/dd/yyyy set as their Short Date format!
<g>

CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is a function that does exactly that. To exclude week ends is easy,
but
to capture holidays, you need a holiday table that has a record for each
date
that is not a work day. This code includes such a table. If you have a
holiday table, change the code to use your names; if not, create a table.
Mine has only two fields - a date and a description.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding
Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Hi,
Here is what i am trying to do:
I want to take todays date and return the appropriate day which is 50
days
from today. I want to use business days only.
ie. Now() - 50
but only using days monday to friday
Does anyone know how to do this