jdellasala
Well-known Member
- Joined
- Dec 11, 2020
- Messages
- 755
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
MARKETCAL2 will generate the days the market is closed for the year requested or the current year if that option is omitted, and is designed to work in conjunction with the MKTDAYS Lambda function to build an array of dates the market is open. It will generate a two column array with the name of the holiday in the first column, and the date in the second column. The optional second parameter can be any value and will omit the holiday name column.
This is the long overdue replacement for the MARKETCAL function mentioned in my MKTDAYS Lambda post and then had to have removed because it was very wrong!
Multiple years can be strung together using VSTACK. Here's the code:
Aside from its use with MKTDAYS, it can also be used for the Holidays options in the NETWORKDAYS.INTL and WORKDAY.INTL functions.
This was inspired and used a lot of the work of @Xlambda on his Lambda Post
www.mrexcel.com
.
Also, I've been trying a way to stack a number of calendars more gracefully than just VSTACK(MARKETCAL2(2021),MARKETCAL2(2022),MARKETCAL2(2023)). Seems like there should be a shorter way to do it using SEQUENCE or something! Please post if you have a better solution!
This is the long overdue replacement for the MARKETCAL function mentioned in my MKTDAYS Lambda post and then had to have removed because it was very wrong!
Multiple years can be strung together using VSTACK. Here's the code:
Excel Formula:
LAMBDA([Yr],[Nms],
LET( y, IF(ISOMITTED(Yr), YEAR(TODAY()), Yr), f, EDATE("1-1-" & y, 0),
fs, f - WEEKDAY(f, 3), s, SEQUENCE(54, 7, fs), m, INDEX(s, , 1),
mm, MONTH(m), mlk, XLOOKUP(1, mm, m) + 14, wb, XLOOKUP(2, mm, m) + 14,
gf, FLOOR("5/" & DAY(MINUTE(y / 38) / 2 + 56) & "/" & y, 7) - 36,
md, XLOOKUP(5, mm, m, , , -1), jt, XLOOKUP(6, mm, m) + 14,
ld, XLOOKUP(9, mm, m), th, INDEX(s, , 4),
mth, MONTH(th), tgd, XLOOKUP(11, mth, th) + 21,
xd, DATE(YEAR(f), {1; 7; 11; 12}, {1; 4; 11; 25}),
wd, WEEKDAY(xd, 3), yd, IF(wd = 6, xd + 1, IF(wd = 5, xd - 1, xd)),
sw, IFS( y<1998,
SWITCH( SEQUENCE(8), 1, INDEX(yd, 1), 2, wb, 3, gf,
4, md, 5, INDEX(yd, 2), 6, ld, 7, tgd, 8, INDEX(yd, 4)),
y < 2022,
SWITCH(
SEQUENCE(9), 1, INDEX(yd, 1), 2, mlk, 3, wb, 4, gf,
5, md, 6, INDEX(yd, 2), 7, ld, 8, tgd, 9, INDEX(yd, 4)),
TRUE,
SWITCH(
SEQUENCE(10), 1, INDEX(yd, 1), 2, mlk, 3, wb, 4, gf,
5, md, 6, jt, 7, INDEX(yd, 2), 8, ld, 9, tgd, 10, INDEX(yd, 4))),
hd, IFS( y < 1998,
{ "NewYear's Day"; "President's Day"; "Good Friday"; "Memorial Day";
"Fourth of July"; "Labor Day"; "Thanksgiving Day"; "Christmas Day"},
y < 2022,
{ "NewYear's Day"; "Martin Luther King Day"; "President's Day";
"Good Friday"; "Memorial Day"; "Fourth of July"; "Labor Day";
"Thanksgiving Day"; "Christmas Day" },
TRUE,
{ "NewYear's Day"; "Martin Luther King Day"; "President's Day";
"Good Friday"; "Memorial Day"; "Juneteenth"; "Fourth of July";
"Labor Day"; "Thanksgiving Day"; "Christmas Day"}),
IF(ISOMITTED(Nms), CHOOSE({1, 2}, hd, sw), sw)
)This was inspired and used a lot of the work of @Xlambda on his Lambda Post
T_US_HOLIDAY_CALC
T_US_HOLIDAY_CALC tool to calculate USA holidays. If someone needs for other country, with its specific rules let me know here. =LAMBDA(y, LET(f,EDATE("1-1-"&y,0),fs,f-WEEKDAY(f,3),s,SEQUENCE(54,7,fs),m,INDEX(s,,1)...
Also, I've been trying a way to stack a number of calendars more gracefully than just VSTACK(MARKETCAL2(2021),MARKETCAL2(2022),MARKETCAL2(2023)). Seems like there should be a shorter way to do it using SEQUENCE or something! Please post if you have a better solution!
Upvote
0
