creating two lists of dates for 2022 (weekends, weekdays) by formula

M

mediumrare

New Member
Joined
Apr 7, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
If I wanted to create two lists by formula (i.e., without using auto-fill) that would return all the weekdays (list 1) and all the weekend days (list 2), how might I do that for 2022?

=WORKDAY(start_date,NETWORKDAYS (start_date, end_date, [holidays]),[holidays])

I tried nesting NETWORKDAYS within WORKDAY (as above) but I must be missing something...
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Upvote 0
Another option for 365
+Fluff 1.xlsm
ABCD
1Year202201/01/2022260
203/01/202202/01/2022
304/01/202208/01/2022
405/01/202209/01/2022
506/01/202215/01/2022
607/01/202216/01/2022
710/01/202222/01/2022
811/01/202223/01/2022
912/01/202229/01/2022
1013/01/202230/01/2022
1114/01/202205/02/2022
1217/01/202206/02/2022
1318/01/202212/02/2022
1419/01/202213/02/2022
1520/01/202219/02/2022
1621/01/202220/02/2022
1724/01/202226/02/2022
1825/01/202227/02/2022
1926/01/202205/03/2022
2027/01/202206/03/2022
2128/01/202212/03/2022
2231/01/202213/03/2022
2301/02/202219/03/2022
2402/02/202220/03/2022
2503/02/202226/03/2022
2604/02/202227/03/2022
2707/02/202202/04/2022
2808/02/202203/04/2022
2909/02/202209/04/2022
3010/02/202210/04/2022
Main
Cell Formulas
RangeFormula
C1C1=DATE(B1,1,1)
D1D1=NETWORKDAYS(C1,DATE(B1,12,31))
A2:A261A2=WORKDAY.INTL(C1,SEQUENCE(D1),1)
B2:B105B2=WORKDAY.INTL(C1,SEQUENCE(DAYS(DATE(B1,12,31),C1)-D1),"1111100")
Dynamic array formulas.
 
Upvote 0
Fluff said:
Another option for 365
Click to expand...
Both of those sequences would need to start at zero (1 January is, & would always be, missing)

Or slightly modified options/helpers

21 09 17.xlsm
ABC
1Year202231/12/2021
2260105
3WeekdaysWeekends
43/01/20221/01/2022
54/01/20222/01/2022
65/01/20228/01/2022
76/01/20229/01/2022
87/01/202215/01/2022
910/01/202216/01/2022
1011/01/202222/01/2022
1112/01/202223/01/2022
1213/01/202229/01/2022
1314/01/202230/01/2022
1417/01/20225/02/2022
1518/01/20226/02/2022
1619/01/202212/02/2022
1720/01/202213/02/2022
1821/01/202219/02/2022
1924/01/202220/02/2022
2025/01/202226/02/2022
2126/01/202227/02/2022
2227/01/20225/03/2022
2328/01/20226/03/2022
2431/01/202212/03/2022
251/02/202213/03/2022
262/02/202219/03/2022
273/02/202220/03/2022
284/02/202226/03/2022
297/02/202227/03/2022
308/02/20222/04/2022
Dates
Cell Formulas
RangeFormula
C1C1=DATE(B1,1,0)
A2A2=NETWORKDAYS(C1+1,DATE(B1,12,31))
B2B2=EDATE(C1,12)-C1-A2
A4:A263A4=WORKDAY.INTL(C1,SEQUENCE(A2))
B4:B108B4=WORKDAY.INTL(C1,SEQUENCE(B2),3,A4#)
Dynamic array formulas.
 
Upvote 0
Solution
Peter_SSs said:
Both of those sequences would need to start at zero (1 January is, & would always be, missing)
Click to expand...

For my own edification:
Why would the sequence need to start at zero?

And would there be any issue or disadvantage to nesting the DATE function (in C1 in yours and Fluff's) throughout?

Book1
AB
1Year2022
2260105
3WeekdaysWeekends
4Monday, January 3, 2022Saturday, January 1, 2022
5Tuesday, January 4, 2022Sunday, January 2, 2022
6Wednesday, January 5, 2022Saturday, January 8, 2022
7Thursday, January 6, 2022Sunday, January 9, 2022
8Friday, January 7, 2022Saturday, January 15, 2022
9Monday, January 10, 2022Sunday, January 16, 2022
10Tuesday, January 11, 2022Saturday, January 22, 2022
11Wednesday, January 12, 2022Sunday, January 23, 2022
12Thursday, January 13, 2022Saturday, January 29, 2022
13Friday, January 14, 2022Sunday, January 30, 2022
14Monday, January 17, 2022Saturday, February 5, 2022
15Tuesday, January 18, 2022Sunday, February 6, 2022
16Wednesday, January 19, 2022Saturday, February 12, 2022
17Thursday, January 20, 2022Sunday, February 13, 2022
18Friday, January 21, 2022Saturday, February 19, 2022
19Monday, January 24, 2022Sunday, February 20, 2022
20Tuesday, January 25, 2022Saturday, February 26, 2022
21Wednesday, January 26, 2022Sunday, February 27, 2022
22Thursday, January 27, 2022Saturday, March 5, 2022
23Friday, January 28, 2022Sunday, March 6, 2022
24Monday, January 31, 2022Saturday, March 12, 2022
25Tuesday, February 1, 2022Sunday, March 13, 2022
26Wednesday, February 2, 2022Saturday, March 19, 2022
27Thursday, February 3, 2022Sunday, March 20, 2022
28Friday, February 4, 2022Saturday, March 26, 2022
29Monday, February 7, 2022Sunday, March 27, 2022
30Tuesday, February 8, 2022Saturday, April 2, 2022
31Wednesday, February 9, 2022Sunday, April 3, 2022
32Thursday, February 10, 2022Saturday, April 9, 2022
33Friday, February 11, 2022Sunday, April 10, 2022
34Monday, February 14, 2022Saturday, April 16, 2022
35Tuesday, February 15, 2022Sunday, April 17, 2022
36Wednesday, February 16, 2022Saturday, April 23, 2022
37Thursday, February 17, 2022Sunday, April 24, 2022
38Friday, February 18, 2022Saturday, April 30, 2022
39Monday, February 21, 2022Sunday, May 1, 2022
40Tuesday, February 22, 2022Saturday, May 7, 2022
41Wednesday, February 23, 2022Sunday, May 8, 2022
42Thursday, February 24, 2022Saturday, May 14, 2022
43Friday, February 25, 2022Sunday, May 15, 2022
44Monday, February 28, 2022Saturday, May 21, 2022
45Tuesday, March 1, 2022Sunday, May 22, 2022
46Wednesday, March 2, 2022Saturday, May 28, 2022
47Thursday, March 3, 2022Sunday, May 29, 2022
48Friday, March 4, 2022Saturday, June 4, 2022
49Monday, March 7, 2022Sunday, June 5, 2022
50Tuesday, March 8, 2022Saturday, June 11, 2022
51Wednesday, March 9, 2022Sunday, June 12, 2022
52Thursday, March 10, 2022Saturday, June 18, 2022
53Friday, March 11, 2022Sunday, June 19, 2022
54Monday, March 14, 2022Saturday, June 25, 2022
55Tuesday, March 15, 2022Sunday, June 26, 2022
56Wednesday, March 16, 2022Saturday, July 2, 2022
57Thursday, March 17, 2022Sunday, July 3, 2022
58Friday, March 18, 2022Saturday, July 9, 2022
59Monday, March 21, 2022Sunday, July 10, 2022
60Tuesday, March 22, 2022Saturday, July 16, 2022
61Wednesday, March 23, 2022Sunday, July 17, 2022
62Thursday, March 24, 2022Saturday, July 23, 2022
63Friday, March 25, 2022Sunday, July 24, 2022
64Monday, March 28, 2022Saturday, July 30, 2022
65Tuesday, March 29, 2022Sunday, July 31, 2022
66Wednesday, March 30, 2022Saturday, August 6, 2022
67Thursday, March 31, 2022Sunday, August 7, 2022
68Friday, April 1, 2022Saturday, August 13, 2022
69Monday, April 4, 2022Sunday, August 14, 2022
70Tuesday, April 5, 2022Saturday, August 20, 2022
71Wednesday, April 6, 2022Sunday, August 21, 2022
72Thursday, April 7, 2022Saturday, August 27, 2022
73Friday, April 8, 2022Sunday, August 28, 2022
74Monday, April 11, 2022Saturday, September 3, 2022
75Tuesday, April 12, 2022Sunday, September 4, 2022
76Wednesday, April 13, 2022Saturday, September 10, 2022
77Thursday, April 14, 2022Sunday, September 11, 2022
78Friday, April 15, 2022Saturday, September 17, 2022
79Monday, April 18, 2022Sunday, September 18, 2022
80Tuesday, April 19, 2022Saturday, September 24, 2022
81Wednesday, April 20, 2022Sunday, September 25, 2022
82Thursday, April 21, 2022Saturday, October 1, 2022
83Friday, April 22, 2022Sunday, October 2, 2022
84Monday, April 25, 2022Saturday, October 8, 2022
85Tuesday, April 26, 2022Sunday, October 9, 2022
86Wednesday, April 27, 2022Saturday, October 15, 2022
87Thursday, April 28, 2022Sunday, October 16, 2022
88Friday, April 29, 2022Saturday, October 22, 2022
89Monday, May 2, 2022Sunday, October 23, 2022
90Tuesday, May 3, 2022Saturday, October 29, 2022
91Wednesday, May 4, 2022Sunday, October 30, 2022
92Thursday, May 5, 2022Saturday, November 5, 2022
93Friday, May 6, 2022Sunday, November 6, 2022
94Monday, May 9, 2022Saturday, November 12, 2022
95Tuesday, May 10, 2022Sunday, November 13, 2022
96Wednesday, May 11, 2022Saturday, November 19, 2022
97Thursday, May 12, 2022Sunday, November 20, 2022
98Friday, May 13, 2022Saturday, November 26, 2022
99Monday, May 16, 2022Sunday, November 27, 2022
100Tuesday, May 17, 2022Saturday, December 3, 2022
101Wednesday, May 18, 2022Sunday, December 4, 2022
102Thursday, May 19, 2022Saturday, December 10, 2022
103Friday, May 20, 2022Sunday, December 11, 2022
104Monday, May 23, 2022Saturday, December 17, 2022
105Tuesday, May 24, 2022Sunday, December 18, 2022
106Wednesday, May 25, 2022Saturday, December 24, 2022
107Thursday, May 26, 2022Sunday, December 25, 2022
108Friday, May 27, 2022Saturday, December 31, 2022
109Monday, May 30, 2022
110Tuesday, May 31, 2022
111Wednesday, June 1, 2022
112Thursday, June 2, 2022
113Friday, June 3, 2022
114Monday, June 6, 2022
115Tuesday, June 7, 2022
116Wednesday, June 8, 2022
117Thursday, June 9, 2022
118Friday, June 10, 2022
119Monday, June 13, 2022
120Tuesday, June 14, 2022
121Wednesday, June 15, 2022
122Thursday, June 16, 2022
123Friday, June 17, 2022
124Monday, June 20, 2022
125Tuesday, June 21, 2022
126Wednesday, June 22, 2022
127Thursday, June 23, 2022
128Friday, June 24, 2022
129Monday, June 27, 2022
130Tuesday, June 28, 2022
131Wednesday, June 29, 2022
132Thursday, June 30, 2022
133Friday, July 1, 2022
134Monday, July 4, 2022
135Tuesday, July 5, 2022
136Wednesday, July 6, 2022
137Thursday, July 7, 2022
138Friday, July 8, 2022
139Monday, July 11, 2022
140Tuesday, July 12, 2022
141Wednesday, July 13, 2022
142Thursday, July 14, 2022
143Friday, July 15, 2022
144Monday, July 18, 2022
145Tuesday, July 19, 2022
146Wednesday, July 20, 2022
147Thursday, July 21, 2022
148Friday, July 22, 2022
149Monday, July 25, 2022
150Tuesday, July 26, 2022
151Wednesday, July 27, 2022
152Thursday, July 28, 2022
153Friday, July 29, 2022
154Monday, August 1, 2022
155Tuesday, August 2, 2022
156Wednesday, August 3, 2022
157Thursday, August 4, 2022
158Friday, August 5, 2022
159Monday, August 8, 2022
160Tuesday, August 9, 2022
161Wednesday, August 10, 2022
162Thursday, August 11, 2022
163Friday, August 12, 2022
164Monday, August 15, 2022
165Tuesday, August 16, 2022
166Wednesday, August 17, 2022
167Thursday, August 18, 2022
168Friday, August 19, 2022
169Monday, August 22, 2022
170Tuesday, August 23, 2022
171Wednesday, August 24, 2022
172Thursday, August 25, 2022
173Friday, August 26, 2022
174Monday, August 29, 2022
175Tuesday, August 30, 2022
176Wednesday, August 31, 2022
177Thursday, September 1, 2022
178Friday, September 2, 2022
179Monday, September 5, 2022
180Tuesday, September 6, 2022
181Wednesday, September 7, 2022
182Thursday, September 8, 2022
183Friday, September 9, 2022
184Monday, September 12, 2022
185Tuesday, September 13, 2022
186Wednesday, September 14, 2022
187Thursday, September 15, 2022
188Friday, September 16, 2022
189Monday, September 19, 2022
190Tuesday, September 20, 2022
191Wednesday, September 21, 2022
192Thursday, September 22, 2022
193Friday, September 23, 2022
194Monday, September 26, 2022
195Tuesday, September 27, 2022
196Wednesday, September 28, 2022
197Thursday, September 29, 2022
198Friday, September 30, 2022
199Monday, October 3, 2022
200Tuesday, October 4, 2022
201Wednesday, October 5, 2022
202Thursday, October 6, 2022
203Friday, October 7, 2022
204Monday, October 10, 2022
205Tuesday, October 11, 2022
206Wednesday, October 12, 2022
207Thursday, October 13, 2022
208Friday, October 14, 2022
209Monday, October 17, 2022
210Tuesday, October 18, 2022
211Wednesday, October 19, 2022
212Thursday, October 20, 2022
213Friday, October 21, 2022
214Monday, October 24, 2022
215Tuesday, October 25, 2022
216Wednesday, October 26, 2022
217Thursday, October 27, 2022
218Friday, October 28, 2022
219Monday, October 31, 2022
220Tuesday, November 1, 2022
221Wednesday, November 2, 2022
222Thursday, November 3, 2022
223Friday, November 4, 2022
224Monday, November 7, 2022
225Tuesday, November 8, 2022
226Wednesday, November 9, 2022
227Thursday, November 10, 2022
228Friday, November 11, 2022
229Monday, November 14, 2022
230Tuesday, November 15, 2022
231Wednesday, November 16, 2022
232Thursday, November 17, 2022
233Friday, November 18, 2022
234Monday, November 21, 2022
235Tuesday, November 22, 2022
236Wednesday, November 23, 2022
237Thursday, November 24, 2022
238Friday, November 25, 2022
239Monday, November 28, 2022
240Tuesday, November 29, 2022
241Wednesday, November 30, 2022
242Thursday, December 1, 2022
243Friday, December 2, 2022
244Monday, December 5, 2022
245Tuesday, December 6, 2022
246Wednesday, December 7, 2022
247Thursday, December 8, 2022
248Friday, December 9, 2022
249Monday, December 12, 2022
250Tuesday, December 13, 2022
251Wednesday, December 14, 2022
252Thursday, December 15, 2022
253Friday, December 16, 2022
254Monday, December 19, 2022
255Tuesday, December 20, 2022
256Wednesday, December 21, 2022
257Thursday, December 22, 2022
258Friday, December 23, 2022
259Monday, December 26, 2022
260Tuesday, December 27, 2022
261Wednesday, December 28, 2022
262Thursday, December 29, 2022
263Friday, December 30, 2022
Sheet1
Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(DATE(B1,1,0)+1,DATE(B1,12,31))
B2B2=EDATE(DATE(B1,1,0),12)-DATE(B1,1,0)-A2
A4:A263A4=WORKDAY.INTL(DATE(B1,1,0),SEQUENCE(A2))
B4:B108B4=WORKDAY.INTL(DATE(B1,1,0),SEQUENCE(B2),3,A4#)
Dynamic array formulas.
 
Upvote 0
mediumrare said:
Why would the sequence need to start at zero?
Click to expand...
Because I used the 1st of Jan as the start date, so if the sequence doesn't start at 0, then 1st Jan will never be in the result, as you can see from the data I posted.
However by using a start date of 31st Dec that problem doesn't arise.
mediumrare said:
And would there be any issue or disadvantage to nesting the DATE function (in C1 in yours and Fluff's) throughout?
Click to expand...
Not that I can think of.
 
Upvote 0
Fluff said:
Because I used the 1st of Jan as the start date, so if the sequence doesn't start at 0, then 1st Jan will never be in the result, as you can see from the data I posted.
However by using a start date of 31st Dec that problem doesn't arise.

Not that I can think of.
Click to expand...

Many thanks, Fluff.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
mediumrare said:
And would there be any issue or disadvantage to nesting the DATE function (in C1 in yours and Fluff's) throughout?
Click to expand...
Nothing that would be noticeable in this particular exercise. It would mean though that the date would have to be calculated several times (once in A2, twice in B2, then also in A4 and B4) rather only once in C1.
It seemed more efficient to only do the calculation once anyway and if, for some reason, that date needed to be calculated differently, it would only need to be amended in one place not several.
Also, the column that formula is in could be hidden if it is the visual effect that you might be trying to avoid.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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
225
Romy
R
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
245
Fluff
Fluff
C
  • Solved
NETWORKDAYS.INTL holiday question
Replies
9
Views
246
Fluff
Fluff
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
338
KRice
K
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
316
ColinPearsonEIT
C

Forum statistics

Threads
1,214,833
Messages
6,121,857
Members
449,051
Latest member
excelquestion515

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