Array to list all workdays for a month

jl2509

jl2509

Board Regular
Joined
Oct 30, 2015
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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


1698840364856.png
1698840492964.png
 
Sort by date Sort by votes
Hi Fluff,
Looks like you cracked it.

Thank you for the help. Most appreciated.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You're welcome & thanks for the feedback.
 
Upvote 0
You can consider or ignore the following. I initially had trouble getting both Jan and May to show correct answers and then was side tracked.
I didn't notice that Fluff had provided a solution that works.

N.B.
Ensure that your holiday list is for 2024.

Test the following
with Name Manager
Name the function to a name that you prefer; I named it ListWorkdaysMo
The function shows the parameters required Year, Month (full name or 3 letters), and holidays.
Value
=LAMBDA(Year,Month,Holidays,LET(y,Year,m,Month,h,Holidays,d,DATE(y,MONTH(--1&LEFT(m,3)),1),n,WORKDAY.INTL(d-1,SEQUENCE(24),1,h),IF(MONTH(n)=MONTH(d),n,"")))
LAMBDA.xlsm
ABCDEFGHI
1Hol2024
21-Jan-24
322-May-24JanuaryFebMarAprMay
4Tue 02-Jan-24Thu 01-Feb-24Fri 01-Mar-24Mon 01-Apr-24Wed 01-May-24
5Wed 03-Jan-24Fri 02-Feb-24Mon 04-Mar-24Tue 02-Apr-24Thu 02-May-24
601-01-24Thu 04-Jan-24Mon 05-Feb-24Tue 05-Mar-24Wed 03-Apr-24Fri 03-May-24
7Fri 31-May-24Fri 05-Jan-24Tue 06-Feb-24Wed 06-Mar-24Thu 04-Apr-24Mon 06-May-24
8108Mon 08-Jan-24Wed 07-Feb-24Thu 07-Mar-24Fri 05-Apr-24Tue 07-May-24
9Tue 09-Jan-24Thu 08-Feb-24Fri 08-Mar-24Mon 08-Apr-24Wed 08-May-24
10Wed 10-Jan-24Fri 09-Feb-24Mon 11-Mar-24Tue 09-Apr-24Thu 09-May-24
11Thu 11-Jan-24Mon 12-Feb-24Tue 12-Mar-24Wed 10-Apr-24Fri 10-May-24
12Fri 12-Jan-24Tue 13-Feb-24Wed 13-Mar-24Thu 11-Apr-24Mon 13-May-24
13Mon 15-Jan-24Wed 14-Feb-24Thu 14-Mar-24Fri 12-Apr-24Tue 14-May-24
14Tue 16-Jan-24Thu 15-Feb-24Fri 15-Mar-24Mon 15-Apr-24Wed 15-May-24
15Wed 17-Jan-24Fri 16-Feb-24Mon 18-Mar-24Tue 16-Apr-24Thu 16-May-24
16Thu 18-Jan-24Mon 19-Feb-24Tue 19-Mar-24Wed 17-Apr-24Fri 17-May-24
17Fri 19-Jan-24Tue 20-Feb-24Wed 20-Mar-24Thu 18-Apr-24Mon 20-May-24
18Mon 22-Jan-24Wed 21-Feb-24Thu 21-Mar-24Fri 19-Apr-24Tue 21-May-24
19Tue 23-Jan-24Thu 22-Feb-24Fri 22-Mar-24Mon 22-Apr-24Thu 23-May-24
20Wed 24-Jan-24Fri 23-Feb-24Mon 25-Mar-24Tue 23-Apr-24Fri 24-May-24
21Thu 25-Jan-24Mon 26-Feb-24Tue 26-Mar-24Wed 24-Apr-24Mon 27-May-24
22Fri 26-Jan-24Tue 27-Feb-24Wed 27-Mar-24Thu 25-Apr-24Tue 28-May-24
23Mon 29-Jan-24Wed 28-Feb-24Thu 28-Mar-24Fri 26-Apr-24Wed 29-May-24
24Tue 30-Jan-24Thu 29-Feb-24Fri 29-Mar-24Mon 29-Apr-24Thu 30-May-24
25Wed 31-Jan-24Tue 30-Apr-24Fri 31-May-24
26
27
28JanFebMarAprMay
29Tue 02-Jan-24Thu 01-Feb-24Fri 01-Mar-24Mon 01-Apr-24Wed 01-May-24
30Wed 03-Jan-24Fri 02-Feb-24Mon 04-Mar-24Tue 02-Apr-24Thu 02-May-24
31Thu 04-Jan-24Mon 05-Feb-24Tue 05-Mar-24Wed 03-Apr-24Fri 03-May-24
32Fri 05-Jan-24Tue 06-Feb-24Wed 06-Mar-24Thu 04-Apr-24Mon 06-May-24
33Mon 08-Jan-24Wed 07-Feb-24Thu 07-Mar-24Fri 05-Apr-24Tue 07-May-24
34Tue 09-Jan-24Thu 08-Feb-24Fri 08-Mar-24Mon 08-Apr-24Wed 08-May-24
35Wed 10-Jan-24Fri 09-Feb-24Mon 11-Mar-24Tue 09-Apr-24Thu 09-May-24
36Thu 11-Jan-24Mon 12-Feb-24Tue 12-Mar-24Wed 10-Apr-24Fri 10-May-24
37Fri 12-Jan-24Tue 13-Feb-24Wed 13-Mar-24Thu 11-Apr-24Mon 13-May-24
38Mon 15-Jan-24Wed 14-Feb-24Thu 14-Mar-24Fri 12-Apr-24Tue 14-May-24
39Tue 16-Jan-24Thu 15-Feb-24Fri 15-Mar-24Mon 15-Apr-24Wed 15-May-24
40Wed 17-Jan-24Fri 16-Feb-24Mon 18-Mar-24Tue 16-Apr-24Thu 16-May-24
41Thu 18-Jan-24Mon 19-Feb-24Tue 19-Mar-24Wed 17-Apr-24Fri 17-May-24
42Fri 19-Jan-24Tue 20-Feb-24Wed 20-Mar-24Thu 18-Apr-24Mon 20-May-24
43Mon 22-Jan-24Wed 21-Feb-24Thu 21-Mar-24Fri 19-Apr-24Tue 21-May-24
44Tue 23-Jan-24Thu 22-Feb-24Fri 22-Mar-24Mon 22-Apr-24Thu 23-May-24
45Wed 24-Jan-24Fri 23-Feb-24Mon 25-Mar-24Tue 23-Apr-24Fri 24-May-24
46Thu 25-Jan-24Mon 26-Feb-24Tue 26-Mar-24Wed 24-Apr-24Mon 27-May-24
47Fri 26-Jan-24Tue 27-Feb-24Wed 27-Mar-24Thu 25-Apr-24Tue 28-May-24
48Mon 29-Jan-24Wed 28-Feb-24Thu 28-Mar-24Fri 26-Apr-24Wed 29-May-24
49Tue 30-Jan-24Thu 29-Feb-24Fri 29-Mar-24Mon 29-Apr-24Thu 30-May-24
50Wed 31-Jan-24Tue 30-Apr-24Fri 31-May-24
5c
Cell Formulas
RangeFormula
E4:I27E4=LET(d,DATE($C$1,MONTH(--1&LEFT(E3,3)),1),n,WORKDAY.INTL(d-1,SEQUENCE(24),1,Hol),IF(MONTH(n)=MONTH(d),n,""))
A8A8=NETWORKDAYS.INTL(A6,A7,1,Hol)
E29:I52E29=ListWorkdaysMo($C$1,E28,Hol)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Hol='5c'!$A$2:$A$3E29:I29, E4:I4, A8
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
909
Fluff
Fluff
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
2K
Romy
R
A
  • Solved
Error with working days in a month
Replies
5
Views
471
Anthony47
A
S
  • Question Question
Listing Months within a range of Dates
Replies
7
Views
350
Peter_SSs
Peter_SSs
P
  • Question Question
workday, but even more flexible
Replies
6
Views
677
KRice
K

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top