Calculate a date in Future with Weekends and Holidays

[MrTwister85]

I have a start date and I have working days. I need to calculate finish date which would extend further because of weekends and holidays. Does anyone know how to do that?
For example, 2/1/23 + 40 working days = Finish Date? (including weekends and holidays). In this case I know 40 working days is 8 weeks so I am missing 16 days of weekends plus whatever US holidays during that time.

 

[MARK858]

You want the Workday function

WORKDAY Function

The Excel WORKDAY function takes a date and returns the nearest working day n days in the future or past. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days.

exceljet.net

 

[brockk]

What version of office are you using? You might want to look into =WORKDAY.INTL for resolution depending on your version of Excel.

 

[MARK858]

@brockk You don't need WORKDAY.INTL for just adding 40 workdays with holidays (as long as the Weekends are the standard Saturday and Sunday). The earlier WORKDAY handles that fine.
Please note that my date format is dd/mm/yyyy.

Book1.xlsb
	C	D	E	F	G	H	I	J
1	11/04/2023					New Year's Day †	Monday	02/01/2023
2						Birthday of Martin Luther King, Jr.	Monday	16/01/2023
3						Washington's Birthday (6 days)	Monday	20/02/2023
4						Memorial Day	Monday	29/05/2023
5						Juneteenth Independence Day	Monday	19/06/2023
6						Independence Day	Tuesday	04/07/2023
7						Labor Day	Monday	04/09/2023
8						Columbus Day	Monday	09/10/2023
9						Veterans Day †	Friday	10/11/2023
10						Thanksgiving Day	Thursday	23/11/2023
11						Christmas Day	Monday	25/12/2023
Sheet4

Cell Formulas
Range	Formula
C1	C1	=WORKDAY(TODAY(),40,J1:J11)

 

[MrTwister85]

You want the Workday function

WORKDAY Function

The Excel WORKDAY function takes a date and returns the nearest working day n days in the future or past. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days.

exceljet.net

Thank you!

 

[MARK858]

You're welcome

 

[brockk]

@MARK858, very true what you stated Sir. I just mentioned the option of the WORKDAY.INTL as it also gives the flexibility to take into consideration if the person's weekend is not both Saturday and Sunday. Also, if there are other non-working days that need to be considered. But, again as you stated for a simple 40 day count WORKDAY alone is sufficient. Excel is great..... !!! You gotta love it!

 

[MARK858]

Also, if there are other non-working days that need to be considered

WORKDAY takes those into account, the syntax is

WORKDAY( start_date, days, [holidays] )