Array to list all workdays for a month

[jl2509]

Hi All,

Can you please help and show how to list all workdays for a month as shown in the example
The dates for each month in the top row should pick up from the last date of the previous month.

I assume this would be a SEQUENCE formula with EOMONTH in cells C4, F4, I4 etc, but I am stuck.
This would also be nice to have if the list of holidays shown in the image were omitted. These are referenced in a Holidays Tab from Cell A1:C8

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Maybe

Fluff.xlsm
	A	B	C	D	E
1			2024
2
3			Jan		Feb
4			Mon, 1 January		Thu, 1 February
5			Tue, 2 January		Fri, 2 February
6			Wed, 3 January		Mon, 5 February
7			Thu, 4 January		Tue, 6 February
8			Fri, 5 January		Wed, 7 February
9			Mon, 8 January		Thu, 8 February
10			Tue, 9 January		Fri, 9 February
11			Wed, 10 January		Mon, 12 February
12			Thu, 11 January		Tue, 13 February
13			Fri, 12 January		Wed, 14 February
14			Mon, 15 January		Thu, 15 February
15			Tue, 16 January		Fri, 16 February
16			Wed, 17 January		Mon, 19 February
17			Thu, 18 January		Tue, 20 February
18			Fri, 19 January		Wed, 21 February
19			Mon, 22 January		Thu, 22 February
20			Tue, 23 January		Fri, 23 February
21			Wed, 24 January		Mon, 26 February
22			Thu, 25 January		Tue, 27 February
23			Fri, 26 January		Wed, 28 February
24			Mon, 29 January		Thu, 29 February
25			Tue, 30 January
26			Wed, 31 January
27
Data

Cell Formulas
Range	Formula
C4:C26	C4	=LET(d,DATE(C1,1,1),n,NETWORKDAYS(d,EOMONTH(d,0),Holidays!A1:A8),WORKDAY(d,SEQUENCE(n,,0),Holidays!A1:A8))
E4:E24	E4	=LET(d,DATE(C1,2,1),n,NETWORKDAYS(d,EOMONTH(d,0),Holidays!A1:A8),WORKDAY(d,SEQUENCE(n,,0),Holidays!A1:A8))
Dynamic array formulas.

 

[jl2509]

Hi Fluff

Details update. I'm using 365 Business on Windows 11

Currently I have the following in Cell C4: =SEQUENCE(DAY(EOMONTH(C$3&$C$1,0)),1,C$3&$C$1,1)
which produces all days of the month but I don't want weekends and as mentioned the nice to have would be to omit the holidays

 

[Fluff]

Details update.

Thanks for that. See the formula in post#3

 

[jl2509]

I have used the formulas as you suggest but it is not discounting the holiday on the 1st Jan?

 

[Fluff]

In that case can you post your data from the holiday sheet (not an image)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jl2509]

Hi
Unfortunately I cannot install add-ins as they are disabled

 

[jl2509]

The only dates which are not being omitted are the dates on the 1st of the month (1st Jan and 1st Apr). All other dates are omitted as expected.
I tested this by Changing 25th Dec to 1st Dec and it was the same result.

25th Dec now shows in the list but so does the 1st Dec.

 

[Fluff]

Ok, how about

=LET(d,DATE(C1,1,1),n,NETWORKDAYS(d,EOMONTH(d,0),Holidays!A1:A8),WORKDAY(d-1,SEQUENCE(n),Holidays!A1:A8))