Workdays formula not excluding holidays as expected

[Factotum]

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!

 

[jasonb75]

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]

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
	A	B	C	D	E	F	G	H
1	Month End Closing Schedule			2021	Year	Holidays	Observed	Day of Week
2	January 2021			1	Month	New Year's Day	1/1/2021	Friday
3				1	Day	May Day	5/3/2021	Monday
4						Memorial Day	5/31/2021	Monday
5	Bus. Day	Date	Day			Independence Day	7/5/2021	Monday
6	Last	12/31/20	Thu			Labor Day	9/6/2021	Monday
7	1st	1/4/21	Mon			All Saints Day	11/1/2021	Monday
8	2nd	1/5/21	Tue
9	3rd	1/6/21	Wed
10	4th	1/7/21	Thu
Chase

Cell Formulas
Range	Formula
A2	A2	=DATE(D1,D2,D3)
G2	G2	=DATE(2021,1,1)
H2:H7	H2	=TEXT(WEEKDAY(G2),"dddd")
G3	G3	=DATE(2021,5,3)
G4	G4	=DATE(2021,5,31)
G5	G5	=DATE(2021,7,5)
G6	G6	=DATE(2021,9,6)
G7	G7	=DATE(2021,11,1)
B6	B6	=WORKDAY($A$2,-1,G2:G7)
C6:C10	C6	=TEXT(B6,"ddd")
B7	B7	=WORKDAY($A$2,1,$G$2:$G$7)
B8	B8	=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),2)
B9	B9	=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),3)
B10	B10	=WORKDAY(DATE(YEAR($A$2),MONTH($A$2),1),4)

 

[jtakw]

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
	A	B	C	D	E	F	G
1	Month End Closing Schedule			2021	Year	Holidays	Observed
2	2/1/2021			2	Month	New Year's Day	1/1/2021
3				1	Day	May Day	5/3/2021
4						Memorial Day	5/31/2021
5	Bus. Day	Date	Day			Independence Day	7/5/2021
6	Last	1/29/2021	Fri			Labor Day	9/6/2021
7	1st	2/1/2021	Mon			All Saints Day	11/1/2021
8	2nd	2/2/2021	Tue
9	3rd	2/3/2021	Wed
10	4th	2/4/2021	Thu
11	5th	2/5/2021	Fri
12	6th	2/8/2021	Mon
13	7th	2/9/2021	Tue
14	8th	2/10/2021	Wed
15	9th	2/11/2021	Thu
Sheet774

Cell Formulas
Range	Formula
A2	A2	=DATE(D1,D2,D3)
B6	B6	=WORKDAY($A$2,-1,G2:G7)
C6:C15	C6	=TEXT(B6,"ddd")
B7:B15	B7	=WORKDAY($A$2-1,ROWS(B$7:B7),$G$2:$G$7)

 

[jasonb75]

That worked fine, based on your mini-sheet, try this in B7, then fill down.

=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.

=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
	A	B	C	D	E	F	G	H
1	Month End Closing Schedule			2021	Year	Holidays	Observed	Day of Week
2	01/02/2021			2	Month	New Year's Day	01/01/2021	Friday
3				1	Day	May Day	03/05/2021	Monday
4						Memorial Day	31/05/2021	Monday
5	Bus. Day	Date	Day			Independence Day	05/07/2021	Monday
6	Last	29/01/2021	Fri			Labor Day	06/09/2021	Monday
7	1st	01/02/2021	Mon			All Saints Day	01/11/2021	Monday
8	2nd	02/02/2021	Tue
9	3rd	03/02/2021	Wed
10	4th	04/02/2021	Thu
Sheet8

Cell Formulas
Range	Formula
A2	A2	=DATE(D1,D2,D3)
G2	G2	=DATE(2021,1,1)
H2:H7	H2	=TEXT(WEEKDAY(G2),"dddd")
G3	G3	=DATE(2021,5,3)
G4	G4	=DATE(2021,5,31)
G5	G5	=DATE(2021,7,5)
G6	G6	=DATE(2021,9,6)
G7	G7	=DATE(2021,11,1)
B6	B6	=WORKDAY($A$2,-1,G2:G7)
C6:C10	C6	=TEXT(B6,"ddd")
B7:B10	B7	=WORKDAY(B6,1,$G$2:$G$7)

 

[Factotum]

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!