Calculating Weekdays

[Lynn63]

I am looking for a way to calculate weekdays. I have a field that captures the date a case was opened. My requirement is to track the number of weekdays the case is open, so currently I'm simply doing a datediff between the case open date and today's date. Problem is the user doesn't want this calculation to include weekend days. I have no clue how to do this. Thanks in advance...

 

[Mile-O]

Create a module and copy and paste the code below into it.

Rather than use the DateDiff, you can put:

CountWeekDays([Start Date],[End Date])

Function CountWeekDays(ByVal dteStartDate As Date, ByVal dteEndDate As Date)
    
    On Error GoTo Err_CountWeekDays
    
    Dim intCounter As Integer, intTotal As Integer
    
    For intCounter = 0 To (dteEndDate - dteStartDate)
        If (WeekDay(dteStartDate + intCounter) = 1) Or _
            (WeekDay(dteStartDate + intCounter) = 7) Then
            ' do nothing
        Else
            intTotal = intTotal + 1
        End If
    Next intCounter
        
    CountWeekDays = intTotal
    
Exit_CountWeekDays:
    Exit Function
    
Err_CountWeekDays:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CountWeekDays
    
End Function

 

[ritchieroo]

Mile-O-Phile,

Your function loops through every date and will be less efficient the further the dates are apart... if you use a mathematical approach you'll get a much more efficient function.



This approach counts five for each complete week between the two dates, then adds the number of working days from the part-weeks at either end of the period:

Public Function CountWeekDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant
    Dim lDaysTotal As Long
    Dim nPartWeekStart As Integer
    Dim nPartWeekEnd As Integer
    Dim vTemp as Variant

'Make sure we got dates!
    If VarType(StartDate) <> vbDate Or _
        VarType(EndDate) <> vbDate Then Exit Function

'Make sure we didn't get Nulls
    If IsNull(StartDate) Or IsNull(EndDate) Then Exit Function

'Swap dates round if backwards
    If StartDate > EndDate Then
        vTemp = StartDate
        StartDate = EndDate
        EndDate = vTemp
    End If

'Work out total number of days inclusive of start and end date
    lDaysTotal = EndDate - StartDate + 1
    
'Adjust for part week at start of period
    nPartWeekStart = IIf(Weekday(StartDate) <> 1, 7 - Weekday(StartDate), 0)
    If nPartWeekStart > 0 Then
        StartDate = StartDate + (8 - Weekday(StartDate))
    End If
    
'Adjust for part week at end of period
    nPartWeekEnd = IIf(Weekday(EndDate) <> 7, Weekday(EndDate) - 1, 0)
    If nPartWeekEnd > 0 Then
        EndDate = EndDate - Weekday(EndDate)
    End If

'Working days is calculated as 5 days for each week between adjusted StartDate
'and adjusted EndDate, then adding the part week bits.
'Note the use of integer division.
    lDaysTotal = EndDate - StartDate + 1
    CountWeekDays = 5 * (lDaysTotal \ 7) + nPartWeekStart + nPartWeekEnd

End Function

 

[ipr1]

Obviously, the module is extremely helpful but how can you improve its focus to not only calculate weekdays but also to delete from that count any holidays that might occur in the interval? For example, there are 21 weekdays from 11/1/03 to 12/1/03 but there is also a Thanksgiving Holiday in there as well.

Is there any way to also delete the holidays?

Thanks again for your help and happy holidays.

 

[ritchieroo]

The holiday question?

I wouldn't want to alter the function CountWeekDays, as it is fit for purpose and useful in its own right.

I would have another function (say 'CountHolidays') that calculates the number of holiday days between two dates. Then the value you want is achieved by simple subtraction.

Because holidays may fall on different days each year, I would have a 'Holiday' table where I could enter the holiday dates. Most probably with three columns as follows:

HolidayDate - date of holiday.
HolidayType - use this to distinguish between Public Holidays and Company Holidays etc.
Description - a simple description, i.e. "Thanksgiving" or "Christmas"

You then need to query the table to find the number of dates between the two dates.

SELECT count(*) As HolidayDayCount
FROM [Holiday]
WHERE [HolidayDate] BETWEEN #11/01/2003# And #12/01/2003#
AND [HolidayType] = "P"

It's a fairly simple exercise to construct a function to wrap this SQL

 

[ipr1]

Thank you for the response. I modified your suggested query language and came up with the following SQL function which works:

SELECT Count(*) AS HolidayDayCount, (CountWeekDays([Forms]![frm1_Main].[StartDate],[Forms]![frm1_Main].[EndDate])) AS Weekdays
FROM Holiday
WHERE ((([Holiday].[HolidayDate]) Between [Forms]![frm1_Main].[StartDate] And [Forms]![frm1_Main].[EndDate]) And (([Holiday].[HolidayType])="P"));

The next aspect of this project involves the construction of a report that will cover an entire production cycle (i.e. a count of weekdays that each of ten departments takes to perform their aspect of a given project). Back to my drawing board!!!

I very much appreciate your getting me started on this exercise. Happy New Year to all.

 

[Jsam69]

There is a flaw in your code

Option Compare Database
Public Function CountWeekDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant
Dim lDaysTotal As Long
Dim nPartWeekStart As Integer
Dim nPartWeekEnd As Integer
Dim vTemp As Variant

'Make sure we got dates!
If VarType(StartDate) <> vbDate Or _
VarType(EndDate) <> vbDate Then Exit Function

'Make sure we didn't get Nulls
If IsNull(StartDate) Or IsNull(EndDate) Then Exit Function

'Swap dates round if backwards
If StartDate > EndDate Then
vTemp = StartDate
StartDate = EndDate
EndDate = vTemp
End If

'Work out total number of days inclusive of start and end date
lDaysTotal = EndDate - StartDate + 1


'Adjust for part week at start of period
nPartWeekStart = IIf(Weekday(StartDate) <> 1, 7 - Weekday(StartDate), 0)
If nPartWeekStart > 0 Then
StartDate = StartDate + (8 - Weekday(StartDate))
End If

'Adjust for part week at end of period
nPartWeekEnd = IIf(Weekday(EndDate) <> 7, Weekday(EndDate) - 1, 0)
If nPartWeekEnd > 0 Then
EndDate = EndDate - Weekday(EndDate)
End If

'Working days is calculated as 5 days for each week between adjusted StartDate
'and adjusted EndDate, then adding the part week bits.
'Note the use of integer division.
lDaysTotal = EndDate - StartDate + 1
CountWeekDays = 5 * (lDaysTotal \ 7) + nPartWeekStart + nPartWeekEnd

End Function

When the start date is in the AM and the end date is date(), the code works fine. But when the start date is PM and the end date is date(), it loses 5 days. For example:

Assume End Date is Date()

start date = 1/7/2005 11:32:54 AM
Count Weekdays = 28

start date = 1/7/2005 12:13:28 PM
Count Weekdays = 23

What is going on?

 

[ritchieroo]

Oops!

My original code treats dates as integer values, and threw a wobbly when you specified time portions.

I've decided to fix this by fixing the dates in the function

Public Function CountWeekDays(ByVal StartDate As Variant, ByVal EndDate As Variant) As Variant
    Dim lDaysTotal As Long
    Dim nPartWeekStart As Integer
    Dim nPartWeekEnd As Integer
    Dim vTemp as Variant

'Make sure we got dates!
    If VarType(StartDate) <> vbDate Or _
        VarType(EndDate) <> vbDate Then Exit Function

'Make sure we didn't get Nulls
    If IsNull(StartDate) Or IsNull(EndDate) Then Exit Function

'Swap dates round if backwards
    If StartDate > EndDate Then
        vTemp = StartDate
        StartDate = EndDate
        EndDate = vTemp
    End If

[b]'Fix dates
    StartDate = Int(StartDate)
    EndDate = Int(EndDate[/b]

'Work out total number of days inclusive of start and end date
    lDaysTotal = EndDate - StartDate + 1
    
'Adjust for part week at start of period
    nPartWeekStart = IIf(Weekday(StartDate) <> 1, 7 - Weekday(StartDate), 0)
    If nPartWeekStart > 0 Then
        StartDate = StartDate + (8 - Weekday(StartDate))
    End If
    
'Adjust for part week at end of period
    nPartWeekEnd = IIf(Weekday(EndDate) <> 7, Weekday(EndDate) - 1, 0)
    If nPartWeekEnd > 0 Then
        EndDate = EndDate - Weekday(EndDate)
    End If

'Working days is calculated as 5 days for each week between adjusted StartDate
'and adjusted EndDate, then adding the part week bits.
'Note the use of integer division.
    lDaysTotal = EndDate - StartDate + 1
    CountWeekDays = 5 * (lDaysTotal \ 7) + nPartWeekStart + nPartWeekEnd

End Function

 

[Calvin]

How to count and subtract Saturday and Sunday.

The easiest way to count how many day-of-week days (ie. Sundays) are between two dates is like the following:

intDaysInPeriod = DateDiff("d", StartDate, EndDate)
intSaturdays = DateDiff("ww", StartDate, EndDate, vbSaturday)
intSundays = DateDiff("ww", StartDate, EndDate, vbSunday)

intWeekDays=intDaysInPeriod - intSaturdays - intSundays