(17B, 19B, 27S, etc.) Weekdays/business days between dates

[Dave Britten]

This formula will calculate the number of weekdays/business days between two dates. It does not calculate a date that is a given number of business days away from another date.

Calculations are made from D1 to D2, whether D2 is later or earlier than D1. If D2 is earlier than D1, a negative count of days is returned.

The methodology behind the calculation works like this: start from the initial date, and begin counting days until the target date. Every time you move "into" a weekday, add one. When counting backwards, the total count is returned as negative. (Note that the formula does not use an iterative method.)

NOTE: There is a date constant used in this formula to calculate the day of the week. If your calculator/palmtop is configured for D.MMYYYY dates, replace 1.071900 with 7.011900.

Examples (All shown in M/D/Y format.)

If today is 2/24/2020, how many business days until 3/14/2020 (Pi day, a Saturday)?

2.242020 [D1]
3.142020 [D2]
[WDAYS] 14.00

(Note that using 3/13/2020 as D2 would produce the same result of 14 days, since 3/14/2020 is a Saturday and is not counted as a 15th day.)


If today is 2/24/2020, and the company mailed a check to a vendor on 2/10/2020, how many business days ago was the check mailed?

2.242020 [D1]
2.102020 [D2]
[WDAYS] -10.00 (i.e. 10 days ago)


D1 should always be the date you are starting to count from, and D2 should be the date you are counting towards. Exchanging them will not give you the same result if one of the dates falls on a weekend! e.g. 2/23/2020 (Sun) -> 2/24/2020 (Mon) = 1, but 2/24/2020 (Mon) -> 2/23/2020 (Sun) = 0.

Code:

WEEKDAYS:0*(
    IF(
        D1>D2
        :L(T1:D2)+L(T2:D1)+L(F:1)+L(Z:8)
        :L(T1:D1)+L(T2:D2)+L(F:0)+L(Z:6)
    )
    +L(DD:DDAYS(G(T1):G(T2):1))
    +IF(L(W:MOD(DDAYS(1.071900:G(T1):1):7))=0:L(W:7):0)
    +IF(L(W2:G(W)+MOD(G(DD):7))=0:L(W2:7):0)
    +L(OFS:
        IF(G(Z)<=G(W2) AND G(Z)>G(W):1:0)
        +IF(7<=G(W2) AND 7>G(W):1:0)
        +IF(G(F)=0:IF(13<=G(W2) AND 13>G(W):1:0):0)
    )
)
+(5*IP(G(DD)/7)+MOD(G(DD):7)-G(OFS))*IF(G(F)=1:-1:1)
=WDAYS

Some notes on the internal (temporary) variables used:

T1: Earlier of the two dates
T2: Later of the two dates
F: 1 if dates were entered in reverse order (D1>D2)
Z: 8 if dates were entered in reverse order, otherwise 6
W: Day of week of the earlier date (1=Mon, 7=Sun)
W2: Date of week of the later date
OFS: Offset/correction factor for start/end dates falling on weekends