Calculate the number of days in work week from start date to end d

[BZeyger]

I am looking to figure out a couple date calculations.

I have three text boxes and a command button:
txtStart
txtEnd
txtResult
cmdRun

I would like to figure out how many work days are included from the start to
end date. A work week would be from Monday to Friday. I know how to calculate
the number of days. I run into trouble when excluding the weekends. Once the
user clicks the command button, the result would appear in txtResult.

 

[Ron2006]

Here is a simple solution (watch word wrap):

1) Compute the number of working days between two dates:


workdays =DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1)) + 1


The first datediff gives you ALL days, The second datediff gives you
the count of Saturdays. The third datediff gives you the count of
Sundays.


Add one or don't add one to the count depending on whether you want
to
count the first AND last day or not.


You now have the total number of weekdays between two dates.


If you want to exclude holidays................
2) Create a table of holidays.


write a query that selects all records in that table between the
specified dates and use


holidaycnt= dCount ("*",
"theabovecreatedquery")


Subtract 2 from1 and you have the actual workdays. (Add 1 to result
if
first and last day are to be counted)


Ron

 

[James A. Fortune]

I am looking to figure out a couple date calculations.

I have three text boxes and a command button:
txtStart
txtEnd
txtResult
cmdRun

I would like to figure out how many work days are included from the start to
end date. A work week would be from Monday to Friday. I know how to calculate
the number of days. I run into trouble when excluding the weekends. Once the
user clicks the command button, the result would appear in txtResult.

Here's what I use:

'Begin Module Code
Public Function CountWeekdays(dtStart As Date, dtEnd As Date) As Integer
'Returns the number of weekdays regardless of whether they are a holiday
or not.
If dtStart <= dtEnd Then
CountWeekdays = DateDiff("d", dtStart, dtEnd) + 1 -
CountWeekendDays(dtStart, dtEnd)
Else
CountWeekdays = DateDiff("d", dtEnd, dtStart) + 1 -
CountWeekendDays(dtEnd, dtStart)
End If
End Function

Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) As Integer
Dim intSat As Integer
Dim intSun As Integer
Dim dtBegin As Date
Dim dtFinish As Date

'Returns the number of weekend days regardless of whether they are a
holiday or not.
CountWeekendDays = 0
If dtStart <= dtEnd Then
dtBegin = dtStart
dtFinish = dtEnd
Else
dtBegin = dtEnd
dtFinish = dtStart
End If
intSat = DateDiff("d", GEDay(dtBegin, 7), LEDay(dtFinish, 7)) / 7 + 1
intSun = DateDiff("d", GEDay(dtBegin, 1), LEDay(dtFinish, 1)) / 7 + 1
CountWeekendDays = Ramp(intSat) + Ramp(intSun)
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

Public Function Ramp(varX As Variant) As Variant
Ramp = IIf(Nz(varX, 0) >= 0, Nz(varX, 0), 0)
End Function
'End Module Code

Sample calls:

CountWeekdays(#6/1/2009#, #6/28/2009#) => 20

SELECT StartDate, EndDate, CountWeekdays([StartDate], [EndDate]) AS
NumberOfWeekdays FROM MyTable;

Private Sub cmdRun_Click()
If Not IsNull(txtStart.Value) And Not IsNull(txtEnd.Value) Then
txtResult.Value = CountWeekdays(txtStart.Value, txtEnd.Value)
Else
MsgBox("You forgot to put a date in. Please try again.")
End If
End Sub

The code allows the start and end dates to be in either order. The code
execution speed should be nearly independent of the date range chosen.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Hi -

Give this a try:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose: Compute number of workdays (Mon - Fri)
' between pStart and pEnd
'Coded by: raskew
'Input: ? fGetWorkdays2(date(), #8/1/09#)
'Output: 47
'************************************************

fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) -
1) + WeekDay(pend) - 1

End Function

HTH - Bob

I agree that using "ww" allows for a more concise expression, but I
avoided solutions that use "ww" because of a bug for a certain
combination of values. See:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/f8675b5c4163ac35

James A. Fortune
(e-mail address removed)