(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

[Albert Chan]

(02-24-2020 04:41 PM)Dave Britten Wrote:  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.

Why the asymmetry ? Shouldn't swapped date gives -1 bussiness days ?

It may be better to use Rata Die, with Jan 1, 1 (Monday) as day 1
This way, DOW, ΔDays, RD, bussiness days (BD) ... can be done from the same scheme.
(Note: business days did not take observed holidays into account ...)

DOW = RD % 7
BD = floor(RD/7)*5 + min(DOW, 5)

RD: 1 2 3 4 5 6 7 8 9 ...
BD: 1 2 3 4 5 5 5 6 7 ...

RD(2/23/2020) = 737478 = 105354*7 + 0
RD(2/24/2020) = 737479 = 105354*7 + 1

BD(2/23/2020) - BD(2/242020) = (105354*5+0) - (105354*5+1) = -1

Quote: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?

RD(2/10/2020) = 737465 = 105352*7 + 1
BD(2/10/2020) - BD(2/242020) = (105352*5+1) - (105354*5+1) = -2*5 = -10

[Dave Britten]

(02-24-2020 07:58 PM)Albert Chan Wrote:  

(02-24-2020 04:41 PM)Dave Britten Wrote:  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.

Why the asymmetry ? Shouldn't swapped date gives -1 bussiness days ?

In this case, no. Imagine that you put your finger on the start date on a month calendar, then start moving your finger one day at a time toward the target date. Count one day each time you move your finger into a weekday. So if my finger starts on 2/24/2020 (Mon), and I move my finger once into 2/23/2020 (Sun), I'm still at zero days because I haven't moved my finger into any weekdays. If I start on 2/23 (Sun) and move to 2/24 (Mon), then I count one, because I've moved my finger into one weekday. That's simply my preferred definition of "weekdays until" or "weekdays ago".

There are other ways to count weekdays, such as counting the number of weekdays included in the span between two dates. With this scheme, if D1 = D2 then the total weekdays = 1 if the given date is a weekday, or 0 if it's a weekend. This is the common method for calculating total capacity or days worked within a range for payroll/utilization purposes.

[Albert Chan]

(02-24-2020 07:58 PM)Albert Chan Wrote:  DOW = RD % 7
BD = floor(RD/7)*5 + min(DOW, 5)

RD: 1 2 3 4 5 6 7 8 9 ...
BD: 1 2 3 4 5 5 5 6 7 ...

(02-24-2020 09:41 PM)Dave Britten Wrote:  Imagine that you put your finger on the start date on a month calendar, then start moving your finger one day at a time toward the target date. Count one day each time you move your finger into a weekday.

My method does the same, except when starting date is after end dates.
Since both BD's are relative to same reference date, it maintained symmetry (swapping dates just flip the sign)

BD(date2) - BD(date1) = -(BD(date1) - BD(date2))

Quote:There are other ways to count weekdays, such as counting the number of weekdays included in the span between two dates.

This is how Excel does it. NETWORKDAYS() included *both* start and end date.

A1: 2/23/2020 (Sunday)
A2: 2/24/2020 (Monday)
A3: 2/25/2020 (Tuesday)

NETWORKDAYS(A1,A2) = 1
NETWORKDAYS(A2,A1) = -1
NETWORKDAYS(A2,A3) = 2       ; Mon + Tue, even though only 1 day passes

[Dave Britten]

Found a small bug that crept in while I was converting this from the original 42S program.

This line...

Code:

+IF(G(F)=1:IF(13<=G(W2) AND 13>G(W):1:0):0)

...should have IF(G(F)=1 replaced with IF(G(F)=0. I've updated the listing accordingly.

[jthole]

Very nice! The usefulness of the solver keeps surprising me :-)