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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,022
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
896
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
5,022
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 
Solution

thesurfdoggie

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

Forum statistics

Threads
1,147,743
Messages
5,742,943
Members
423,765
Latest member
PaulD1984

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
Top