I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Date_Formulas

Count of weekdays:
A1 Start date
A2 End date
A3 Weekday to count between A1 and A2 (1=Sunday)
Formula: =INT((A2-MOD(A2-A3,7)-A1+7)/7)
Alternative Formula (by Daniel M.): =INT((A2-WEEKDAY(A2+1-A3)-A1+8)/7)

Example: You have several options to calculate how many Wednesdays and Fridays are between 1-Jan-2013 and 15-Apr-2013:
Put the start date into A1 and the end date into A2.
Solution 1: =INT((A2-MOD(A2-4,7)-A1+7)/7)+INT((A2-MOD(A2-6,7)-A1+7)/7)
Solution 2: array-enter =SUM(INT((A2-MOD(A2-{4,6},7)-A1+7)/7))
Solution 3: =SUMPRODUCT(INT((A2-MOD(A2-{4,6},7)-A1+7)/7))
The result is in all cases: 30
Please notice that 4 stands for Wednesday and 6 for Friday. If you like to add other or additional weekdays change {4,6} accordingly. If you want to count more than 3 weekdays you might prefer to subtract the sum of the remaining weekdays from end date - start date + 1.

Last Friday of a month:
A1 Any day within given month
Formula: =DATE(YEAR(A1),MONTH(A1)+1,0)-MOD(DATE(YEAR(A1),MONTH(A1)+1,0)+1,7)
[if A1 holds last day of a month then the formula simply is =A1-MOD(A1+1,7)]
For a general solution to determine the first or the last weekday (actually, any) of a month, see here, please.

Determine whether a date is a weekend day (Saturday or Sunday):
A1 Date
Formula: =MOD(A1,7)<2

Count of working days (Mondays through Fridays without any holidays):
A1 Start date
A2 End date
Formula: =(A2-WEEKDAY(A2,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5, WEEKDAY(A1,2))+MIN(5,WEEKDAY(A2,2))
This formula is different from Excel's built-in function NETWORKDAYS (Analysis ToolPak add-in before Excel 2007). It counts from A1 24:00 until A2 24:00. If you look for a NETWORKDAYS-equivalent solution, take A2 minus A1 plus 1 and subtract the count of Saturdays and Sundays (see count of weekdays above).

Rick Rothstein came up with an elegant equivalent VBA version:

Function CWD(StartDate As Date, EndDate As Date) As Long
     CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 -
        (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) + (Weekday(StartDate, 2) < 6)
End Function

Rick also provided this more general version which, if the optional NWD parameter is omitted or set to False, will return the same values as the above version of CWD and if the optional NWD parameter is set to True, then it will return the same results as Excel's NETWORKDAYS function (again, without consideration for holidays):

Function CWD(StartDate As Date, EndDate As Date, _
             Optional NWD As Boolean) As Long
    CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
        EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) _
        - (Not NWD) * (Weekday(StartDate, 2) < 6)
End Function

A more complex function which calculates the sum of working days for a series of alternating start dates and end dates:

Function CWPeriods(dBase As Date, rStartEnd As Range) As Variant
'Counts working days between alternating start dates and end dates in rStartEnd.
'If a start date is given but no corresponding end date then count until dBase.
'In general: start dates are counting, end dates not!
'Reverse("moc.LiborPlus.www") V0.2 PB 25-Aug-2009
Dim lc As Long
Dim i As Long, j As Long
Dim bIsStart As Boolean, bEndSeen As Boolean
Dim dStart As Date, dEnd As Date

If rStartEnd.Cells.Count Mod 2 <> 0 Then
    CWPeriods = "Range with start dates and end dates has to have even number of cells!"
    Exit Function
End If

bIsStart = True     'First cell in rStartEnd is a start date
bEndSeen = True     'We do not want to count anything if rStartEnd is empty.
lc = 0              'Initialize day count to zero

For i = 1 To rStartEnd.Rows.Count
    For j = 1 To rStartEnd.Columns.Count
        If Not IsEmpty(rStartEnd.Cells(i, j)) Then
            If bIsStart Then
                dStart = rStartEnd.Cells(i, j) - 1
                bEndSeen = False
            Else
                dEnd = rStartEnd.Cells(i, j) - 1
                If dEnd < dStart Then
                    CWPeriods = "Error! End date " & Format(dEnd + 1, "yyyy-mm-dd") & _
                        " is before start date " & Format(dStart + 1, "yyyy-mm-dd") & "."
                    Exit Function
                End If
                lc = lc + cwd(dStart, dEnd)
                bEndSeen = True
            End If
        End If
        bIsStart = Not bIsStart 'Toggle between start and end date treatment
    Next j
Next i

If Not bEndSeen Then
    lc = lc + cwd(dStart, dBase - 1)
End If

CWPeriods = lc

End Function

Sulprobil   Get it done   Contact   Disclaimer   Download