Make WORKDAY function round down to Friday instead of up to Sunday

[thesurfdoggie]

I am using a formula to create due dates for a task. The formula is, =WORKDAY(E9,D10,M9:M17) where E9 is a date, D10 is a number of days and M9:M17 is a list of holidays.
When the WORKDAY formula output lands on a Saturday or Sunday, the output defaults to the following Monday. I need to have the output to default to the Friday before, not the Monday after. I tried using the WEEKDAY function to resolve, but since the WORKDAY already resulted in a Monday output, the WEEKDAY function doesn't do anything.

Thoughts??

 

[Dave Patton]

Date and Time.xlsm
	D	E	F	G
8
9		3-Nov-20
10	14		Fri 20-Nov-20	Fri 20-Nov-20
11
15bb

Cell Formulas
Range	Formula
F10	F10	=WORKDAY.INTL(WORKDAY(E9,D10,M9:M17),-1,"1000011",M9:M17)
G10	G10	=IF(WEEKDAY(WORKDAY(E9,D10,M9:M17),2)=1,WORKDAY(E9,D10,M9:M17)-3,WORKDAY(E9,D10,M9:M17))

You could make G10 much more concise with Excel's new Let function or with a UDF that has been on the forum for many years.

 

[6StringJazzer]

When the WORKDAY formula output lands on a Saturday or Sunday, the output defaults to the following Monday.

I'm not sure what you mean by that. WORKDAY is the next working day. If you start on Friday 11/20, and add 6 working days, the next working day is Monday 11/30. Nothing is landing on a Saturday or Sunday; weekends are ignored. There is no "rounding."

 

[Dave Patton]

Date and Time.xlsm
	D	E	F	G
9		3-Nov-20
10	24		Fri 4-Dec-20	Fri 4-Dec-20
11				Fri 4-Dec-20
12
15bb

Cell Formulas
Range	Formula
F10	F10	=WORKDAY.INTL(WORKDAY(E9,D10+1,M9:M17),-1,"1000011",M9:M17)
G10	G10	=IF(WEEKDAY(WORKDAY(E9,D10,M9:M17),2)=1,WORKDAY(E9,D10,M9:M17)-3,WORKDAY(E9,D10,M9:M17))
G11	G11	=WORKDAY(E9,(D10-1),M9:M17)+(WEEKDAY(WORKDAY(E9,(D10-1),M9:M17),2)<>5)

 

[thesurfdoggie]

Date and Time.xlsm
	D	E	F	G
9		3-Nov-20
10	24		Fri 4-Dec-20	Fri 4-Dec-20
11				Fri 4-Dec-20
12
15bb

Cell Formulas
Range	Formula
F10	F10	=WORKDAY.INTL(WORKDAY(E9,D10+1,M9:M17),-1,"1000011",M9:M17)
G10	G10	=IF(WEEKDAY(WORKDAY(E9,D10,M9:M17),2)=1,WORKDAY(E9,D10,M9:M17)-3,WORKDAY(E9,D10,M9:M17))
G11	G11	=WORKDAY(E9,(D10-1),M9:M17)+(WEEKDAY(WORKDAY(E9,(D10-1),M9:M17),2)<>5)

Dave... Thanks very much for your help with this. You are a life saver.

 

[Dave Patton]

Thanks for the feedback.