lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,957
- Office Version
- 365
- Platform
- Windows
NTHDAYOFMONTH takes 4 arguments which can be single cell references or ranges. The function will return the nth day of the month for a given year, month, day of week, and nth day of month.
Excel Formula:
=LAMBDA(year,month,day_index,nth_day,
MAP(year,month,day_index,nth_day,
LAMBDA(y,m,d,n,
WORKDAY.INTL(
DATE(y,m,0),
n,
REPLACE("111111",d,0,"0")
)
)
)
)
COMPSTAT Template.xltx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Year | Month | Day Index | Nth Day | FX | Day | ||
2 | 2022 | 11 | 1 | 1 | 11/7/2022 | Mon | ||
3 | 2022 | 11 | 2 | 1 | 11/1/2022 | Tue | ||
4 | 2022 | 11 | 3 | 1 | 11/2/2022 | Wed | ||
5 | 2022 | 11 | 4 | 1 | 11/3/2022 | Thu | ||
6 | 2022 | 11 | 5 | 1 | 11/4/2022 | Fri | ||
7 | 2022 | 11 | 6 | 1 | 11/5/2022 | Sat | ||
8 | 2022 | 11 | 7 | 1 | 11/6/2022 | Sun | ||
9 | 2022 | 11 | 1 | 2 | 11/14/2022 | Mon | ||
10 | 2022 | 11 | 2 | 2 | 11/8/2022 | Tue | ||
11 | 2022 | 11 | 3 | 2 | 11/9/2022 | Wed | ||
12 | 2022 | 11 | 4 | 2 | 11/10/2022 | Thu | ||
13 | 2022 | 11 | 5 | 2 | 11/11/2022 | Fri | ||
14 | 2022 | 11 | 6 | 2 | 11/12/2022 | Sat | ||
15 | 2022 | 11 | 7 | 2 | 11/13/2022 | Sun | ||
16 | 2022 | 11 | 1 | 3 | 11/21/2022 | Mon | ||
17 | 2022 | 11 | 2 | 3 | 11/15/2022 | Tue | ||
18 | 2022 | 11 | 3 | 3 | 11/16/2022 | Wed | ||
19 | 2022 | 11 | 4 | 3 | 11/17/2022 | Thu | ||
20 | 2022 | 11 | 5 | 3 | 11/18/2022 | Fri | ||
21 | 2022 | 11 | 6 | 3 | 11/19/2022 | Sat | ||
22 | 2022 | 11 | 7 | 3 | 11/20/2022 | Sun | ||
23 | 2022 | 11 | 1 | 4 | 11/28/2022 | Mon | ||
24 | 2022 | 11 | 2 | 4 | 11/22/2022 | Tue | ||
25 | 2022 | 11 | 3 | 4 | 11/23/2022 | Wed | ||
26 | 2022 | 11 | 4 | 4 | 11/24/2022 | Thu | ||
27 | 2022 | 11 | 5 | 4 | 11/25/2022 | Fri | ||
28 | 2022 | 11 | 6 | 4 | 11/26/2022 | Sat | ||
29 | 2022 | 11 | 7 | 4 | 11/27/2022 | Sun | ||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C29 | C2 | =MOD(SEQUENCE(28,,0),7)+1 |
D2:D29 | D2 | =INT(SEQUENCE(28,,0)/7)+1 |
E2:E29 | E2 | =NTHDAYINMONTH(A2:A29,B2:B29,C2#,D2#) |
F2:F29 | F2 | =TEXT(E2#,"ddd") |
Dynamic array formulas. |
Upvote
0