Display Dates (minus weekends and holidays) Using TODAY() Function

[Disarmonious]

I would like to know if there's a formula that would be able to provide tomorrow's date (the date for the next 4 days) but I need it to be only business days (Mon-Fri) no weekends (Sat or Sun) and no holidays. I have 4 cells next to each other where the first cell represents tomorrow's date, the 2nd cell represents the date for the day after tomorrow, and so forth. I am using TODAY() and TODAY()+1 to achieve dates but this formula does not factor in Saturdays, Sundays, and Holidays, and I need the date to be business days (not landing on ANY weekend or holiday).

Thank you in advance!

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Disarmonious]

Microsoft Excel for Microsoft 365 MSO (version 2402) 64-bit

 

[Asbestos_Jen]

The workday function will do what you want.

Book1
	A	B	C	D	E	F	G
1
2		Today	29-Jul-24	29-Jul-24		HolidayList
3		Today+1	30-Jul-24	30-Jul-24		1-Jan-24	New Year's Day
4		Today+2	31-Jul-24	31-Jul-24		19-Feb-24	Family Day (AB,NB,ON,SK)
5		Today+3	1-Aug-24	1-Aug-24		29-Mar-24	Good Friday
6		Today+4	2-Aug-24	2-Aug-24		20-May-24	Victoria Day
7		Today+5	6-Aug-24	6-Aug-24		1-Jul-24	Canada Day
8		Today+6	7-Aug-24	7-Aug-24		5-Aug-24	Heritage Day
9		Today+7	8-Aug-24	8-Aug-24		2-Sep-24	Labour Day
10		Today+8	9-Aug-24	9-Aug-24		14-Oct-24	Thanksgiving
11		Today+9	12-Aug-24	12-Aug-24		11-Nov-24	Remembrance Day
12		Today+10	13-Aug-24	13-Aug-24		25-Dec-24	Christmas
13						26-Dec-24	Boxing Day
Sheet1

Cell Formulas
Range	Formula
C2:D2	C2	=TODAY()
C3:C12	C3	=WORKDAY($C2,1,HolidayList)
D3	D3	=WORKDAY(TODAY(),1,HolidayList)
D4	D4	=WORKDAY(TODAY(),2,HolidayList)
D5	D5	=WORKDAY(TODAY(),3,HolidayList)
D6	D6	=WORKDAY(TODAY(),4,HolidayList)
D7	D7	=WORKDAY(TODAY(),5,HolidayList)
D8	D8	=WORKDAY(TODAY(),6,HolidayList)
D9	D9	=WORKDAY(TODAY(),7,HolidayList)
D10	D10	=WORKDAY(TODAY(),8,HolidayList)
D11	D11	=WORKDAY(TODAY(),9,HolidayList)
D12	D12	=WORKDAY(TODAY(),10,HolidayList)

Named Ranges
Name	Refers To	Cells
HolidayList	=Sheet1!$F$3:$F$13	C3:D12

 

[Disarmonious]

Thank you very much! This should work the way I want it!

 

[Asbestos_Jen]

Glad to have helped.