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

thesurfdoggie

New Member
Joined
Nov 19, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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??
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Date and Time.xlsm
DEFG
8
93-Nov-20
1014Fri 20-Nov-20Fri 20-Nov-20
11
15bb
Cell Formulas
RangeFormula
F10F10=WORKDAY.INTL(WORKDAY(E9,D10,M9:M17),-1,"1000011",M9:M17)
G10G10=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.
 
Upvote 0
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."
 
Upvote 0
Date and Time.xlsm
DEFG
93-Nov-20
1024Fri 4-Dec-20Fri 4-Dec-20
11Fri 4-Dec-20
12
15bb
Cell Formulas
RangeFormula
F10F10=WORKDAY.INTL(WORKDAY(E9,D10+1,M9:M17),-1,"1000011",M9:M17)
G10G10=IF(WEEKDAY(WORKDAY(E9,D10,M9:M17),2)=1,WORKDAY(E9,D10,M9:M17)-3,WORKDAY(E9,D10,M9:M17))
G11G11=WORKDAY(E9,(D10-1),M9:M17)+(WEEKDAY(WORKDAY(E9,(D10-1),M9:M17),2)<>5)
 
Upvote 0
Solution
Date and Time.xlsm
DEFG
93-Nov-20
1024Fri 4-Dec-20Fri 4-Dec-20
11Fri 4-Dec-20
12
15bb
Cell Formulas
RangeFormula
F10F10=WORKDAY.INTL(WORKDAY(E9,D10+1,M9:M17),-1,"1000011",M9:M17)
G10G10=IF(WEEKDAY(WORKDAY(E9,D10,M9:M17),2)=1,WORKDAY(E9,D10,M9:M17)-3,WORKDAY(E9,D10,M9:M17))
G11G11=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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top