Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
ACALENDAR or month calendar, for any date, displays icons next to the day, icons for matching 2 other date sets, holidays, and other dates.
If someone wants the formula for Sunday as first day of the week, or other configuration, let me know.
If someone wants the formula for Sunday as first day of the week, or other configuration, let me know.
Excel Formula:
=LAMBDA(d,m,y,h,o,
LET(td,UNICHAR(128197),hy,UNICHAR(9726),ot,UNICHAR(9762),
yx,IF(y=0,YEAR(TODAY()),y),mx,IF(m<>"",m,MONTH(TODAY())),dx,IF(d=0,DAY(TODAY()),d),
xd,EDATE(dx&"-"&mx&"-"&yx,0),fd,EOMONTH(xd,-1)+1,w,WEEKDAY(fd,3),sd,fd-w-14,sq,SEQUENCE(8,7,sd),
ca,MONTH(sq)=MONTH(fd),cb,DAY(sq)=dx,cc,ISNUMBER(XMATCH(sq,h)),cd,ISNUMBER(XMATCH(sq,o)),
qd,TEXT(sq,"dd"),
aa,IF(cb,qd&" "&td,qd),ab,IF(cc,aa&" "&hy,aa),ac,IF(cd,ab&" "&ot,ab),ad,IF(ca,ac,""),
fr,CHOOSE(SEQUENCE(,7),"","",TEXT(fd,"mmm"),TEXT(fd,"yyyy"),"","",""),sr,TEXT(SEQUENCE(,7,2),"ddd"),
rs,SWITCH(SEQUENCE(8),1,fr,2,sr,ad),
IF(ISERROR(xd),"check data",rs)
)
)
LAMBDA 5.0.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | icons unichars values used in formula | ||||||||||||||||
2 | these can be changed inside the formula, are defined at the beginning of LET | ||||||||||||||||
3 | today | ? | 128197 | ||||||||||||||
4 | holidays | ◾ | 9726 | ||||||||||||||
5 | other | ☢ | 9762 | d | m | y | holidays | other | |||||||||
6 | all values set | 26 | march | 21 | 07-03-21 | 01-03-21 | |||||||||||
7 | =ACALENDAR(G6,H6,I6,J6:J10,K6:K9) | 14-03-21 | 23-03-21 | ||||||||||||||
8 | Mar | 2021 | 21-03-21 | 10-03-21 | |||||||||||||
9 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | 28-03-21 | 20-03-21 | ||||||||
10 | 01 ☢ | 02 | 03 | 04 | 05 | 06 | 07 ◾ | 25-03-21 | |||||||||
11 | 08 | 09 | 10 ☢ | 11 | 12 | 13 | 14 ◾ | ||||||||||
12 | 15 | 16 | 17 | 18 | 19 | 20 ☢ | 21 ◾ | ||||||||||
13 | 22 | 23 ☢ | 24 | 25 ◾ | 26 ? | 27 | 28 ◾ | ||||||||||
14 | 29 | 30 | 31 | ||||||||||||||
15 | |||||||||||||||||
16 | |||||||||||||||||
17 | everything ignored, displays todays day month,year,no icons for holydays and other dates | ||||||||||||||||
18 | =ACALENDAR(,,,,) | ||||||||||||||||
19 | Mar | 2021 | |||||||||||||||
20 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | ||||||||||
21 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | ||||||||||
22 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | ||||||||||
23 | 15 | 16 | 17 | 18 ? | 19 | 20 | 21 | ||||||||||
24 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | ||||||||||
25 | 29 | 30 | 31 | ||||||||||||||
26 | |||||||||||||||||
27 | |||||||||||||||||
28 | if,day or month, or year are ignored, formula chooses curent day,month,or year | ||||||||||||||||
29 | if we want to display same day and month of today() but of next year | ||||||||||||||||
30 | =ACALENDAR(,,22,J32:J34,K32:K35) | ||||||||||||||||
31 | Mar | 2022 | holidays 22 | other 22 | |||||||||||||
32 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | 06-03-22 | 15-03-22 | ||||||||
33 | 01 | 02 | 03 | 04 | 05 | 06 ◾ | 13-03-22 | 31-03-22 | |||||||||
34 | 07 | 08 | 09 | 10 | 11 | 12 | 13 ◾ | 27-03-22 | 04-04-22 | ||||||||
35 | 14 | 15 ☢ | 16 | 17 | 18 ? | 19 | 20 | 10-04-22 | |||||||||
36 | 21 | 22 | 23 | 24 | 25 | 26 | 27 ◾ | ||||||||||
37 | 28 | 29 | 30 | 31 ☢ | |||||||||||||
38 | |||||||||||||||||
39 | |||||||||||||||||
40 | default use, day,month and year ignored , but the holydays and other dates selected | ||||||||||||||||
41 | will always show the todays day and curent holidays and other dates coresponding to the curent month | ||||||||||||||||
42 | place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly | ||||||||||||||||
43 | want to check future events, change the date and month in the formula | ||||||||||||||||
44 | =ACALENDAR(,,,J6:J10,K6:K9) | ||||||||||||||||
45 | Mar | 2021 | |||||||||||||||
46 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | ||||||||||
47 | 01 ☢ | 02 | 03 | 04 | 05 | 06 | 07 ◾ | ||||||||||
48 | 08 | 09 | 10 ☢ | 11 | 12 | 13 | 14 ◾ | ||||||||||
49 | 15 | 16 | 17 | 18 ? | 19 | 20 ☢ | 21 ◾ | ||||||||||
50 | 22 | 23 ☢ | 24 | 25 ◾ | 26 | 27 | 28 ◾ | ||||||||||
51 | 29 | 30 | 31 | ||||||||||||||
52 | |||||||||||||||||
53 | |||||||||||||||||
ACALENDAR post |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:C5 | C3 | =UNICODE(B3) |
B7,B44,B30,B18 | B7 | =FORMULATEXT(B8) |
B8:H15 | B8 | =ACALENDAR(G6,H6,I6,J6:J10,K6:K9) |
B19:H26 | B19 | =ACALENDAR(,,,,) |
B31:H38 | B31 | =ACALENDAR(,,22,J32:J34,K32:K35) |
B45:H52 | B45 | =ACALENDAR(,,,J6:J10,K6:K9) |
Dynamic array formulas. |
Upvote
0