Workday returning a Saturday

[realPrincessApril]

Hi all,

I thought I understood the Workday function, but apparently not. If my ref date is in B34 and holidays in lookup table, I thought this would return the first workday of the month:

=WORKDAY.INTL($B$34,1,1,'Unit Lookups'!$A$21:$A$33)

For ref date 1/1/2022 it returns Monday 1/3/2022 as first workday (great).
But if I change ref date to 2/1/2022, the formula returns Wednesday 2/2/2022 rather than Tuesday 2/1/2022 (whoops). I can get the formula to return 2/1/22 for ref date 2/1/22 if I change the days offset in the formula from 1 to 0, but then that would return incorrectly for January. (2/1/22 is not a holiday in my lookup table).

So I'm wondering if there is an issue with my reference to the weekends. I had thought 1 was the default code for Sat/Sun, which I am using as the weekends. Could someone kindly help me understand what I'm missing? Thank you so much!

 

[Dave Patton]

I do not know your holidays; they are not included in the formulas below.
Consider either of the following.

Workdays.xlsm
	A	B
1		First workday of month
2
3	Sat 01-Jan-22	Mon 03-Jan-22
4	Tue 01-Feb-22	Tue 01-Feb-22
5	Tue 01-Mar-22	Tue 01-Mar-22
6	Fri 01-Apr-22	Fri 01-Apr-22
7	Sun 01-May-22	Mon 02-May-22
8
9	Fri 31-Dec-21	Mon 03-Jan-22
10	Mon 31-Jan-22	Tue 01-Feb-22
11	Mon 28-Feb-22	Tue 01-Mar-22
12	Thu 31-Mar-22	Fri 01-Apr-22
13
1c

Cell Formulas
Range	Formula
B3:B7	B3	=WORKDAY.INTL(A3-1,1,1)
B9:B12	B9	=WORKDAY.INTL(A9,1,1,A$21:$A$33)

 

[realPrincessApril]

Hi Dave,

Your second formula looks like what I tried (but maybe I'm just not seeing the difference). Here is a sample minisheet that shows that the second formula seems to work well for first workday in 1/22 but not 2/22 (A11 should return 2/1 rather than 2/2 as the first workday in Feb 2022). I'm having trouble seeing my error if you could help. Thanks so much.

minisheet workday.xlsx
	A	B	C	D	E	F
1				HOLIDAYS 2022
2	January 2022			Date	Holiday	Day
3	Date	Day		1/17/22	Martin Luther King Day	Monday
4	1/3	Mon		5/30/22	Memorial Day	Monday
5				6/20/22	Juneteenth (Observed)	Monday
6				7/4/22	Independence Day	Monday
7				9/5/22	Labor Day	Monday
8				10/11/22	National Coming Out Day	Tuesday
9	February 2022			11/24/22	Thanksgiving Day	Thursday
10	Date	Day		11/25/22	Day after Thanksgiving	Friday
11	2/2	Wed		12/26/22	Christmas Day (Observed)	Monday
12				12/30/22	New Year's Day (Observed)	Friday
13					Extra line if needed
14					Extra line if needed
15					Extra line if needed
Sheet1

Cell Formulas
Range	Formula
A4,A11	A4	=WORKDAY.INTL(A2,1,1,$D$3:$D$15)
B4,B11	B4	=A4
F3:F12	F3	=D3

 

[Phuoc]

Try this in A4: =WORKDAY.INTL(A2,1,1,$D$3:$D$15)

in A11: =WORKDAY.INTL(A9-DAY(A9),1,1,$D$3:$D$15)

 

[realPrincessApril]

Cool Phuoc. That second formula looks more flexible (seems to work for both months). Am I correct in assuming that formula should work for all months of all years, assuming I correctly point to the ref cell and update the offset day?

Also, is there a handy way to copy this function down to generate the whole month (where the blue cells are)? I can't seem to get Excel to recognize the pattern when I use the crosshair thing. I tried to select the first few days of Feb and copy down with the crosshair, but it repeats the 1-3 pattern rather than continuing down the numbering.

minisheet workday.xlsx
	A	B	C	D	E	F
1				HOLIDAYS 2022
2	January 2022			Date	Holiday	Day
3	Date	Day		1/17/22	Martin Luther King Day	Monday
4	1/3	Mon		5/30/22	Memorial Day	Monday
5				6/20/22	Juneteenth (Observed)	Monday
6				7/4/22	Independence Day	Monday
7				9/5/22	Labor Day	Monday
8				10/11/22	National Coming Out Day	Tuesday
9	February 2022			11/24/22	Thanksgiving Day	Thursday
10	Date	Day		11/25/22	Day after Thanksgiving	Friday
11	2/1	Tue		12/26/22	Christmas Day (Observed)	Monday
12	2/2	Wed		12/30/22	New Year's Day (Observed)	Friday
13	2/3	Thu			Extra line if needed
14		Sat			Extra line if needed
15		Sat			Extra line if needed
16		Sat
17		Sat
18		Sat
19		Sat
20		Sat
21		Sat
22		Sat
23		Sat
24		Sat
25		Sat
26		Sat
27		Sat
28		Sat
29		Sat
30		Sat
31
Sheet1

Cell Formulas
Range	Formula
A4	A4	=WORKDAY.INTL(A2-DAY(A2),1,1,D3:D15)
B4,B11:B30	B4	=A4
F3:F12	F3	=D3
A11	A11	=WORKDAY.INTL(A9-DAY(A9),1,1,D3:D15)
A12	A12	=WORKDAY.INTL(A9-DAY(A9),2,1,D3:D15)
A13	A13	=WORKDAY.INTL(A9-DAY(A9),3,1,D3:D15)

 

[Biz]

You could also use Networkdays.INTL
NETWORKDAYS.INTL function

 

[Dave Patton]

Please review the formulas suggested. You stated that you want the first workday for the month.

Excel's help states " WORKDAY.INTL function - Office Support
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters."

If you want the first workday of a month, enter the last day of the previous month and try the formula or

Enter the first day of the month and subtract 1 or enter any date in the month and subtract day of (Date), this yields the last day of the previous month,
1 specifies next workday, etc.

Workdays.xlsm
	A	B
14	Sat 01-Jan-22	Sat 15-Jan-22
15	Fri 31-Dec-21	Fri 31-Dec-21
16	Mon 03-Jan-22	Mon 03-Jan-22
1c

Cell Formulas
Range	Formula
A15:B15	A15	=A14-DAY(A14)
A16:B16	A16	=WORKDAY.INTL(A15,1,1)