Workdays formula not excluding holidays as expected

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
I'm building a checklist which needs to list the date of the last, first, second, and third business days of the given month. I've entered all dates as formulas because that's what Microsoft Help suggested. The formula works for January, but not February. Anywhere there is a formula in the screenshots below, I have added a duplicate line so I can show the exact formula and the results. You can see in the first screenshot that it correctly calculates January 4th as the first working day of the month, since January 1 is in the holiday list.

In the second screenshot, I've changed the starting date to February 1, but the formula calculates the first business day as February 2nd, even though the first business day is on February 1st and there is no exception in my holiday list. I've been all over the place trying to solve this one - please help! Many thanks!


1613592750856.png




1613592953437.png
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
You're referring to $D$1 as the source date, but it appears to be empty?

It is preferable to use XL2BB to post your examples rather than screen captures (see link in my signature below this post). It helps to preserve formatting and formulas more accurately, we can also copy and paste it to excel to test the formulas without retyping everything.

That aside, you need to use $D$1-1 in your formulas so that you're starting from the last day of the previous month. The WORKDAY function returns the working day before or after (not including the specified date). You can use 0 in the second part but then it fails if it is not a working day.
 

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
Awww, nuts! That was a scaled back version of the original. I'm not sure what went wrong with the screenshot, but I'm sure that it is pointing to the correct starting date. If it was pointing to a blank cell, it would return Jan 2, 1900. Below is my first attempt at a mini-sheet. Hopefully it works.

Workdays Example.xlsx
ABCDEFGH
1Month End Closing Schedule2021YearHolidaysObservedDay of Week
2January 20211MonthNew Year's Day1/1/2021Friday
31DayMay Day5/3/2021Monday
4Memorial Day5/31/2021Monday
5Bus. DayDateDayIndependence Day7/5/2021Monday
6Last12/31/20ThuLabor Day9/6/2021Monday
71st1/4/21MonAll Saints Day11/1/2021Monday
82nd1/5/21Tue
93rd1/6/21Wed
104th1/7/21Thu
Chase
Cell Formulas
RangeFormula
A2A2=DATE(D1,D2,D3)
G2G2=DATE(2021,1,1)
H2:H7H2=TEXT(WEEKDAY(G2),"dddd")
G3G3=DATE(2021,5,3)
G4G4=DATE(2021,5,31)
G5G5=DATE(2021,7,5)
G6G6=DATE(2021,9,6)
G7G7=DATE(2021,11,1)
B6B6=WORKDAY($A$2,-1,G2:G7)
C6:C10C6=TEXT(B6,"ddd")
B7B7=WORKDAY($A$2,1,$G$2:$G$7)
B8B8=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),2)
B9B9=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),3)
B10B10=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),4)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,908
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Have you tried, as jasonb75 suggested in Post #2, subtracting 1 day from the Start date in the WORKDAY formula?

Also, to make it so you can just drag the formula down without changing the day number in the formula in each cell:

Book3.xlsx
ABCDEFG
1Month End Closing Schedule2021YearHolidaysObserved
22/1/20212MonthNew Year's Day1/1/2021
31DayMay Day5/3/2021
4Memorial Day5/31/2021
5Bus. DayDateDayIndependence Day7/5/2021
6Last1/29/2021FriLabor Day9/6/2021
71st2/1/2021MonAll Saints Day11/1/2021
82nd2/2/2021Tue
93rd2/3/2021Wed
104th2/4/2021Thu
115th2/5/2021Fri
126th2/8/2021Mon
137th2/9/2021Tue
148th2/10/2021Wed
159th2/11/2021Thu
Sheet774
Cell Formulas
RangeFormula
A2A2=DATE(D1,D2,D3)
B6B6=WORKDAY($A$2,-1,G2:G7)
C6:C15C6=TEXT(B6,"ddd")
B7:B15B7=WORKDAY($A$2-1,ROWS(B$7:B7),$G$2:$G$7)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
That worked fine, based on your mini-sheet, try this in B7, then fill down.
Excel Formula:
=WORKDAY(B6,1,$G$2:$G$7)
As you already have the last working day of the previous month in B6 it is easier to use that to find the 1st day of the next month, then use the 1st to find the 2nd, the 2nd to find the 3rd and so on.

The way that you were trying, by using using the 1st of the month and trying to establish whether or not it is a working day you would need to use.
Excel Formula:
=WORKDAY($A$2-1,1,$G$2:$G$7)
but with the existing 'last' day already in the sheet it is easier to make use of that.

Also, the DATE function is not necessary if you're only entering a date into a cell (G2:G7), you only need it if you're entering a date into a longer formula.
One of the main issues with dates is that they get messed up with regional formats if you send a file from the US to the UK (for example), as long as the dates are valid with your regional settings then there will be no problems, but a date entered into a formula as a text string will only work for anyone with the same regional settings as you, going between US and UK with text dates will cause month and day to be switched in error.
Book1 (version 1).xlsb
ABCDEFGH
1Month End Closing Schedule2021YearHolidaysObservedDay of Week
201/02/20212MonthNew Year's Day01/01/2021Friday
31DayMay Day03/05/2021Monday
4Memorial Day31/05/2021Monday
5Bus. DayDateDayIndependence Day05/07/2021Monday
6Last29/01/2021FriLabor Day06/09/2021Monday
71st01/02/2021MonAll Saints Day01/11/2021Monday
82nd02/02/2021Tue
93rd03/02/2021Wed
104th04/02/2021Thu
Sheet8
Cell Formulas
RangeFormula
A2A2=DATE(D1,D2,D3)
G2G2=DATE(2021,1,1)
H2:H7H2=TEXT(WEEKDAY(G2),"dddd")
G3G3=DATE(2021,5,3)
G4G4=DATE(2021,5,31)
G5G5=DATE(2021,7,5)
G6G6=DATE(2021,9,6)
G7G7=DATE(2021,11,1)
B6B6=WORKDAY($A$2,-1,G2:G7)
C6:C10C6=TEXT(B6,"ddd")
B7:B10B7=WORKDAY(B6,1,$G$2:$G$7)
 
Solution

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
Thank you both for your help! I must have glossed over the suggestion to subtract 1 day from the starting date in the first reply - was too focused on figuring out XL2BB (a new feature since last time I was here).

Jason - both of your suggestions worked great! It's always the simple solutions that seem to elude me. My personal preference in this case is to have all the formulas tie back to the date at the top of the sheet. If I had to copy this down to each day of the month (rather than just the 4th business day) then I would definitely go with your suggestion to use first day to find the second, second to find the third, etc.

Problem solved, thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,487
Messages
5,625,054
Members
416,067
Latest member
newb08

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