Weekends in date based formulas

[Sweedler]

Hello

I have a large document for delivery of goods. In one column I have the agreed upon date for delivery of the finished product. In the columns next to I have calculated dates for several steps that precede the product being ready to deliver. These formulas first calculate the number of days from the end date that a step needs to be completed by and then that answer is removed from the end date to get the date for that step's completion. But the formula only calculates the number of days, and I need it to only count that answer as weekdays. Does anyone know a formula that would take that into consideration.

Thank you

 

[Fluff]

Sounds like you need either the workday or networkdays function.

 

[Sweedler]

Sounds like you need either the workday or networkdays function.

Hi

So how would that work. If I have the date 2024-03-26 (a Tuesday) as my end date and packaging needs to take place 5 days in advance of that. So the date it returns should be the 19th, but the formula "=DATECELL-5" would return the 21:st. How do I adjust and adapt to get the correct date back?

 

[awoohaw]

Something like this maybe:

Book1
	A	B
1
2		DATECELL
3		2024-03-26
4
5
6		2024-03-19
Sheet4

Cell Formulas
Range	Formula
B3	B3	=DATE(2024,3,26)
B6	B6	=WORKDAY.INTL(DATECELL,-5,1)

Named Ranges
Name	Refers To	Cells
DATECELL	=Sheet4!$B$3	B6

 

[Fluff]

You would simply use

=workday(datecell,-5)

 

[Dave Patton]

Work Days.xlsm
	A	B	C
1
2			Tue 26-Mar-2024
3			5
4			Tue 19-Mar-2024
5
1b

Cell Formulas
Range	Formula
C4	C4	=WORKDAY(C2,-C3)

The formula could also consider holidays.

 

[Sweedler]

You would simply use

=workday(datecell,-5)

This worked amazingly. The file is working great. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.