How to exclude weekends & bank holidays

[Baggeepants]

Can the following formula be amended to exclude weekends & bank holidays? Column A is a 'Due Date' & column B is the 'Issue Date' & column C has the formula. On a separate sheet in the workbook I have a list of the Bank Holidays.

=IF(COUNT(A2,B2)=2,TEXT(B2-A2,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),"")

 

[Kerryx]

Did you try searching the site?

 

[Kerryx]

Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.

 

[KRice]

Have a look at the NETWORKDAYS.INTL function. Here I've designated Saturdays and Sundays as weekend/no-work days and created a notional list of holidays that are also excluded from the day count. You may want to subtract one day, depending on when "day 1" occurs.

Book1
	A	B	C	D	E
1	Forecast Date	Issue Date			Holidays
2	1/8/2024	1/15/2024	5		12/25/2023
3	1/15/2024	12/22/2023	-14		1/1/2024
4	1/15/2024	1/15/2024	1		1/10/2024
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=NETWORKDAYS.INTL(A2,B2,"0000011",$E$2:$E$4)

 

[Baggeepants]

Thanks, although not exactly what I need (days early/late & on time required), I will give these a go as they will give a more accurate number of days.

 

[KRice]

Oh, that's just the main idea behind the number. You can still run a test on the number (positive/negative) to determine early/late and add text to the final result. What version of Excel are you using?

Edit: The Excel version matters because there is some redundancy involved in 1) determining the number of days, 2) checking the sign of the number of days, and 3) incorporating that number in a text string...so it would be good to know whether you have access to some of the enhanced functions in Excel 365/2021.

 

[Baggeepants]

Excel 365

 

[KRice]

Editing

 

[KRice]

Sorry...I realized I made a mistake with the days adjustment. The NETWORKDAYS.INTL function will give 1 as a result when the start and end dates are the same--you probably want that to be 0, so I subtracted 1 from the function result. But that creates an error in some cases because negative days (early) become even more negative (which is incorrect). So I've introduced an intermediate calculation to address this (I hope). Check carefully with a few examples of early, late, on time with weekends and holidays to confirm results are as expected:

Book1
	A	B	C	D	E
1	Forecast Date	Issue Date			Holidays
2	1/8/2024	1/15/2024	4 Day(s) Late		12/25/2023
3	1/15/2024	12/22/2023	13 Day(s) Early		1/1/2024
4	1/15/2024	1/15/2024	On Time		1/10/2024
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=LET(n,NETWORKDAYS.INTL(A2,B2,"0000011",$E$2:$E$4),d,n-SIGN(n),SWITCH(SIGN(d),-1,ABS(d) & " Day(s) Early",1,ABS(d) & " Day(s) Late",0,"On Time"))

 

[Baggeepants]

Thank you Kirk, problem how solved!