Formula to calculate dates

[Schoolsec]

Hello,

I work for a school district.
I would like to calculate 30 days out from a specific date and exclude weekends and school holidays or random school days off.

Example:
3/1/24 plus 30 days, minus weekends and spring break (March 25-29) would be 4/19/24.
3/4/24 plus 30 days, minus weekends and spring break (March 25-29) would be 4/22/24.
5/1/24 plus 30 days, minus weekends and Memorial Day (May 27) would be 6/13/24.

Ideally, I would need to be able to plug in all school days off throughout the year and weekends and produce an entire spreadsheet.

Thank you for anyone's help.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G
1	Start	Add	End			Holidays
2	3/1/2024	30	4/19/2024			Spring Break	3/25/2024
3	3/4/2024	30	4/22/2024			Spring Break	3/26/2024
4	5/1/2024	30	6/13/2024			Spring Break	3/27/2024
5						Spring Break	3/28/2024
6						Spring Break	3/29/2024
7						Memorial Day	5/27/2024
Sheet4

Cell Formulas
Range	Formula
C2:C4	C2	=WORKDAY(A2,B2,$G$2:$G$7)

 

[Schoolsec]

Hi, Thanks for the quick response.
This is exactly what I needed. O my I am so excited.
Thank you Thank you Thank you!

 

[Eric W]

Happy to help! Thanks for the update.

 

[Schoolsec]

Happy to help! Thanks for the update.

Hi Eric,

Do you know of a way to exclude weekends in my column A?

 

[Goran974_MK]

Hi Eric,

Do you know of a way to exclude weekends in my column A?

Hi,

You can use the Fill series which is in the home tab. (see pic 1).
When you open the Fill series dialogue box, click the column, on the right click workdays, and as an end day write down your end day. (see pic 2)
In picture 3 you can see March 2024 workdays.

g

 

[Schoolsec]

ok, I'm back at it with additional help needed.

I can't get my formula to copy down the column. It wants to move down the E column. I need it to stay E2:E29, Hoping to not have to do this manually.

Hopefully this make sense.
Thanks!

 

[Asbestos_Jen]

If I understand your request, in the first row of the formula change the E2:29 to $E2:$E$29. That will "lock" the reference in place. Then you can fill down and the range will stay the same.