ACALENDAR

=ACALENDAR(d,m,y,h,o)
d
day, if ignored will choose the day value of today()
m
month, if ignored will choose the month value of today(), accepts formats like, 3,mar,march,Mar,March
y
year, if ignored will choose the year value of today(), accepts formats like 21 or 2021
h
holidays date set, 1D array , column or row or defined name, in any date format accepted by excel. wrong format will not match
o
others , a second date set, same like the above
ACALENDAR or month calendar, for any date, displays icons next to the day, icons for matching 2 other date sets, holidays, and other dates.
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. 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.
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
ABCDEFGHIJKLMNO
1icons unichars values used in formula
2these can be changed inside the formula, are defined at the beginning of LET
3today?128197
4holidays◾9726
5other☢9762dmyholidaysother
6all values set26march2107-03-2101-03-21
7=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)14-03-2123-03-21
8 Mar202121-03-2110-03-21
9MonTueWedThuFriSatSun28-03-2120-03-21
1001 ☢020304050607 ◾25-03-21
11080910 ☢11121314 ◾
12151617181920 ☢21 ◾
132223 ☢2425 ◾26 ?2728 ◾
14293031
15
16
17everything ignored, displays todays day month,year,no icons for holydays and other dates
18=ACALENDAR(,,,,)
19 Mar2021
20MonTueWedThuFriSatSun
2101020304050607
2208091011121314
2315161718 ?192021
2422232425262728
25293031
26
27
28if,day or month, or year are ignored, formula chooses curent day,month,or year
29if we want to display same day and month of today() but of next year
30=ACALENDAR(,,22,J32:J34,K32:K35)
31 Mar2022holidays 22other 22
32MonTueWedThuFriSatSun06-03-2215-03-22
33010203040506 ◾13-03-2231-03-22
3407080910111213 ◾27-03-2204-04-22
351415 ☢161718 ?192010-04-22
3621222324252627 ◾
3728293031 ☢
38
39
40default use, day,month and year ignored , but the holydays and other dates selected
41will always show the todays day and curent holidays and other dates coresponding to the curent month
42place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly
43want to check future events, change the date and month in the formula
44=ACALENDAR(,,,J6:J10,K6:K9)
45 Mar2021
46MonTueWedThuFriSatSun
4701 ☢020304050607 ◾
48080910 ☢11121314 ◾
4915161718 ?1920 ☢21 ◾
502223 ☢2425 ◾262728 ◾
51293031
52
53
ACALENDAR post
Cell Formulas
RangeFormula
C3:C5C3=UNICODE(B3)
B7,B44,B30,B18B7=FORMULATEXT(B8)
B8:H15B8=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)
B19:H26B19=ACALENDAR(,,,,)
B31:H38B31=ACALENDAR(,,22,J32:J34,K32:K35)
B45:H52B45=ACALENDAR(,,,J6:J10,K6:K9)
Dynamic array formulas.
 
Upvote 0
This is how it looks properly aligned. The formula is important. The format design , colors, icons, alignment or meaning of data sets can be changed to fit anybody's needs.
Excel Formula:
LAMBDA 5.0.xlsm
ABCDEFGHIJKLMN
1icons unichars values used in formula
2these can be changed inside the formula, are defined at the beginning of LET
3today?128197
4holidays◾9726
5other☢9762dmyholidaysother
6all values set26march2107-03-2101-03-21
7=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)14-03-2123-03-21
8 Mar202121-03-2110-03-21
9MonTueWedThuFriSatSun28-03-2120-03-21
1001 ☢020304050607 ◾25-03-21
11080910 ☢11121314 ◾
12151617181920 ☢21 ◾
132223 ☢2425 ◾26 ?2728 ◾
14293031
15
16
17everything ignored, displays todays day month,year,no icons for holydays and other dates
18=ACALENDAR(,,,,)
19 Mar2021
20MonTueWedThuFriSatSun
2101020304050607
2208091011121314
231516171819 ?2021
2422232425262728
25293031
26
27
28if,day or month, or year are ignored, formula chooses curent day,month,or year
29if we want to display same day and month of today() but of next year
30=ACALENDAR(,,22,J32:J34,K32:K35)
31 Mar2022holidays 22other 22
32MonTueWedThuFriSatSun06-03-2215-03-22
33010203040506 ◾13-03-2231-03-22
3407080910111213 ◾27-03-2204-04-22
351415 ☢16171819 ?2010-04-22
3621222324252627 ◾
3728293031 ☢
38
39
40default use, day,month and year ignored , but the holydays and other dates selected
41will always show the todays day and curent holidays and other dates coresponding to the curent month
42place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly
43want to check future events, change the date and month in the formula
44=ACALENDAR(,,,J6:J10,K6:K9)
45 Mar2021
46MonTueWedThuFriSatSun
4701 ☢020304050607 ◾
48080910 ☢11121314 ◾
491516171819 ?20 ☢21 ◾
502223 ☢2425 ◾262728 ◾
51293031
52
53
ACALENDAR post
Cell Formulas
RangeFormula
C3:C5C3=UNICODE(B3)
B7,B44,B30,B18B7=FORMULATEXT(B8)
B8:H15B8=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)
B19:H26B19=ACALENDAR(,,,,)
B31:H38B31=ACALENDAR(,,22,J32:J34,K32:K35)
B45:H52B45=ACALENDAR(,,,J6:J10,K6:K9)
Dynamic array formulas.
 
This is a great one as usual. How do you get the colored calendar icon?
 
schardt679 said:
This is a great one as usual. How do you get the colored calendar icon?
Click to expand...
Thanks a lot!!! What you will always see here is due to the BB code transformation, but on your spreadsheet, you can use Conditional Formatting as you like. The icons are like any other font, so you can change or choose any color or formatting. Here is an example.
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNO
1in cell B3=ACALENDAR(,,,B13:B15,C13:C16)
2
3 May2021
4MonTueWedThuFriSatSunIconsCF formulas
50102 ☢unicode=UNICHAR(J6:J8)range B3:H10
60304 ?050607 ◾0809128197?CF form 1 =ISNUMBER(SEARCH(UNICHAR(128197),B3))
710111213141516 ☢9726◾CF form 2 =ISNUMBER(SEARCH(UNICHAR(9726),B3))
817 ◾181920 ☢2122239762☢CF form 3 =ISNUMBER(SEARCH(UNICHAR(9762),B3))
9242526 ☢2728 ◾2930
1031
11
12holidaysother
1317-05-2102-05-21
1407-05-2116-05-21
1528-05-2126-05-21
1620-05-21
17
Sheet11
Cell Formulas
RangeFormula
B1B1=FORMULATEXT(B3)
B3:H10B3=ACALENDAR(,,,B13:B15,C13:C16)
K5K5=FORMULATEXT(K6)
K6:K8K6=UNICHAR(J6:J8)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:H10Expression=ISNUMBER(SEARCH(UNICHAR(9762),B3))textNO
B3:H10Expression=ISNUMBER(SEARCH(UNICHAR(9726),B3))textNO
B3:H10Expression=ISNUMBER(SEARCH(UNICHAR(128197),B3))textNO
 
This is how it looks on spreadsheet (screen capture)
CF pic 2.jpg
 
If somebody needs the calendar weekdays to start with Sun instead of Mon we have to modify the formula only on 2 places subtracting 1 (-1). Could have been done with an argument but I wanted to keep it simple.
-on the formula row that starts with xd , modify sd variable from fd-w-14 to fd-w-15
xd,EDATE(dx&"-"&mx&"-"&yx,0),fd,EOMONTH(xd,-1)+1,w,WEEKDAY(fd,3),sd,fd-w-15,sq,SEQUENCE(8,7,sd),
-on the formula row that starts with fr , modify sr variable from TEXT(SEQUENCE(,7,2) to TEXT(SEQUENCE(,7,1)
fr,CHOOSE(SEQUENCE(,7),"","",TEXT(fd,"mmm"),TEXT(fd,"yyyy"),"","",""),sr,TEXT(SEQUENCE(,7,1),"ddd"),
Here is a screen capture:
ACAL CF pic 3.jpg

Obs: You can notice that the formula updated dynamically to today's date 5-05-21 (posting date), since the formula has the first 3 arguments d,m,y ignored ( =ACALENDAR(,,,B13:B15,C13:C16) )
 
Happy New Year everyone!!
Inspired by today's Chandoo YT (5-Jan-2022), I thought ACALENDAR also deserves an update, this time with lot more functionality.
Check his variant here: FREE 2022 Calendar and Planner Template with Excel
ACALENDAR is designed not as a template, is based only on formulas/functions and 2 input tables "Cat" and "Ev", can be called on any cell, any spreadsheet, more instances at the same time.
Functionality:
- can handle any dates from past or future, any years
- we can define as many event categories as we want
- added event duration and more events/same day functionality
- bonus function, AGENDA, which can call the events table, filtering for dates or one or more events, or searching dynamically for one or more words through events description (notes)
First, I will cover the designed concepts, and, at the end, the functions.
1st concept is about expanding a sequence of dates based on duration, and building a relationship between 2 arrays, like a Data model.
2nd concept is about adding corresponding icons to the corresponding dates.
This post will be about input tables setup, names, headers, input example, icons.
ACALENDAR 2022.xlsx
ABCDEFGHIJKLM
1Input tables set up.
2events category table (Cat)events table (Ev)icons unicode values
3Ev sample-imagine that this was filed in during dec 2021unicodeunicharlength
4categoryiconstartdayseventnotes9989✅1
5to do✅17-01-221meetingCEO meeting, 2021's report128680?2
6important?15-08-2214vacationvacation France9200⏰1
7reminder⏰11-07-221deadlineplanning vacation, booking hotel8987⌛1
8deadline⌛25-12-213day offXmas128101?2
9meeting?26-12-211othercall mom128747?2
10travel?31-12-215day offNew Year 9208⏸1
11day off⏸️04-01-221reminderrenew fitness subscription127958?2
12vacation?04-01-221importanttake laptop to work128681?2
13other?15-01-223to doprepare report's dashboard
1415-01-221deadlinefinish report formulas128221?2
15other categories examples23-08-224traveltrip to London9742☎1
16that can be added anytime02-08-221reminderplanning trip to London, book flight128231?2
1726-01-223meetingdepartments meetings, 2022 budget128279?2
18memo?27-01-221importantbring financial report 2021128161?2
19call☎↑↑↑128269?2
20email?↑↑clm with DV =Cat[category]128204?2
21link?dates in any order, different days duration, same date, more events128295?2
22idea?10002✒1
23research?Notes:128176?2
24remember?- both tables names and headers should be defined as such127384?2
25maintenance?- days clm represent duration of the event including starting date127873?2
26contract✒- start dates can be in any order (filter table buttons can be used for sorting)127947?2
27paycheck?- event clm has Data Validation list=Cat[category]128138?2
28safety?- if we want an event not to be displayed by ACALENDAR function, 9971⛳1
29birthdate?but still to be part of "Ev" table, we can delete its duration days (blank),127942?2
30workout?and only if we want to permanently delete an event, we can delete the table row127881?2
31medicine?
32sport⛳
33tournament?
34party?
35
ACALENDAR 1
Cell Formulas
RangeFormula
J4:J12J4=UNICODE(Cat[icon])
K4:K12,K14:K30K4=UNICHAR(J4#)
L4:L12,L14:L30L4=LEN(K4#)
J14:J30J14=UNICODE(C18:C34)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
G5:G18List=$B$5:$B$13
 
ACALENDAR 2022.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1New ACALENDAR.Concept 1. expanding sequence of dates depending on duration parameter plus data model relationship between 2 arraysstep 4. flattening dates sequences INDEX(dt,q)+m-1
21st array sample has 3 clms, start date, duration of event (including starting date), event category ; 2nd sample array has 2 clms, event category and icons=INDEX(I8:I12,O10#)+Q10#-1
3Start dates in random order, can have duplicates as start dates or when they expand. Also days clm can have blanks that should be ignored.↓step 5. flattening events INDEX(ev,q)
4↓=INDEX(K8:K12,O10#)
5from 1st and 2nd arraystothisstep 2. flatten array parameters↓↓step 6. flattening ca INDEX(ca,q,m)
61st array↓↓↓↓↓↓step 1.sort and filter days<>""rows (5) x clms ( max(days)=4 )step 3. distribution conditional array↓↓=INDEX(T15#,O10#,Q10#)
7startdaysevent08-01-22?=SORT(FILTER(B8:D13,C8:C13<>""))=SEQUENCE(5*4)=MOD(SEQUENCE(5,4)-1,4)+1fdt08-01-22meetingTRUEfca
810-01-223important09-01-22?445692meeting↓=QUOTIENT(M10#-1,4)+1123409-01-22meetingTRUEstep 7. vlookup events icons 2nd array
912-01-221deadline09-01-22✅445703to do↓↓=MOD(M10#-1,4)+1123410-01-22meetingFALSE=VLOOKUP(AB7#,B19:C27,2,0)
1009-01-223to do10-01-22✅445713important11q1m123411-01-22meetingFALSE?ia
1108-01-222meeting11-01-22✅445731deadline212123409-01-22to doTRUE?
1215-01-22reminder10-01-22?445754travel313123410-01-22to doTRUE?
1314-01-224travel11-01-22?dtev41411-01-22to doTRUE?
1412-01-22?521=T8#<=J8:J12ca12-01-22to doFALSE✅
15relationship: event category12-01-22⌛622TRUETRUEFALSEFALSE10-01-22importantTRUE✅
1614-01-22?723TRUETRUETRUEFALSE11-01-22importantTRUE✅
172nd array15-01-22?824TRUETRUETRUEFALSE12-01-22importantTRUE✅
18categoryicon16-01-22?931TRUEFALSEFALSEFALSE13-01-22importantFALSE?
19to do✅17-01-22?1032TRUETRUETRUETRUE12-01-22deadlineTRUE?
20important?113313-01-22deadlineFALSE?
21reminder⏰123414-01-22deadlineFALSE?
22deadline⌛134115-01-22deadlineFALSE⌛
23meeting?last step144214-01-22travelTRUE⌛
24travel?step 8. array result construction (seq dates,icons)154315-01-22travelTRUE⌛
25day off⏸️filtered, with fca as FILTER include argument164416-01-22travelTRUE⌛
26vacation? =FILTER(IF({1,0},fdt,ia),fca)175117-01-22travelTRUE?
27other?1852?
28=FILTER(IF({1,0},Z7#,AF10#),AD7#)1953?
2908-01-22?2054?
3009-01-22?
3109-01-22✅
3210-01-22✅
3311-01-22✅
3410-01-22?
3511-01-22?
3612-01-22?
3712-01-22⌛
3814-01-22?
3915-01-22?
4016-01-22?
4117-01-22?
42
ACALENDAR 2
Cell Formulas
RangeFormula
Z2Z2=FORMULATEXT(Z7)
AB4,M7AB4=FORMULATEXT(AB7)
AD6,F28,T14,AF9,Q9,T7,I7AD6=FORMULATEXT(AD7)
Z7:Z26Z7=INDEX(I8:I12,O10#)+Q10#-1
AB7:AB26AB7=INDEX(K8:K12,O10#)
AD7:AD26AD7=INDEX(T15#,O10#,Q10#)
I8:K12I8=SORT(FILTER(B8:D13,C8:C13<>""))
O8O8=FORMULATEXT(O10)
T8:W12T8=MOD(SEQUENCE(5,4)-1,4)+1
M10:M29M10=SEQUENCE(5*4)
O10:O29O10=QUOTIENT(M10#-1,4)+1
Q10:Q29Q10=MOD(M10#-1,4)+1
AF10:AF29AF10=VLOOKUP(AB7#,B19:C27,2,0)
T15:W19T15=T8#<=J8:J12
F29:G41F29=FILTER(IF({1,0},Z7#,AF10#),AD7#)
Dynamic array formulas.
 
ACALENDAR 2022.xlsx
ABCDEFGHIJKLMNOPQ
1New ACALENDAR. Concept 2. Appending corresponding icons of corresponding events to a dates array
2
3dates that have more than 1 icon (more events/same day)
4
5array from previous post
6a=SEQUENCE(5,7,"1-1-22")b
708-01-22?01-01-2202-01-2203-01-2204-01-2205-01-2206-01-2207-01-22
809-01-22?08-01-2209-01-2210-01-2211-01-2212-01-2213-01-2214-01-22
909-01-22✅15-01-2216-01-2217-01-2218-01-2219-01-2220-01-2221-01-22
1010-01-22✅22-01-2223-01-2224-01-2225-01-2226-01-2227-01-2228-01-22
1111-01-22✅29-01-2230-01-2231-01-2201-02-2202-02-2203-02-2204-02-22
1210-01-22?
1311-01-22?- will use a nested MAP(REDUCE)) construction
1412-01-22?- what the formula does, for every element of "b", (map) we check a correspondence with all elements of "a",
1512-01-22⌛and if there is one we "accumulate"(append) the results (reduce)
1614-01-22?
1715-01-22?- lambda "formula" =MAP(b,LAMBDA(x,REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(v,i,v&IF(x=INDEX(a,i,1),INDEX(a,i,2),"")))))
1816-01-22?=MAP(E7#,LAMBDA(x,REDUCE("",SEQUENCE(ROWS(B7:C19)),LAMBDA(v,i,v&IF(x=INDEX(B7:C19,i,1),INDEX(B7:C19,i,2),"")))))
1917-01-22? 
20??✅✅?✅??⌛?
21???
22
23c
24
25 - appending the results =DAY(b)&c
26=DAY(E7#)&E19#
271234567
288?9?✅10✅?11✅?12?⌛1314?
2915?16?17?18192021
3022232425262728
312930311234
32
33Note: Dates array in this example is not offset acording to weekday, ACALENDAR will take care of that
34
ACALENDAR 3
Cell Formulas
RangeFormula
E6,E26,E18E6=FORMULATEXT(E7)
E7:K11E7=SEQUENCE(5,7,"1-1-22")
E19:K23E19=MAP(E7#,LAMBDA(x,REDUCE("",SEQUENCE(ROWS(B7:C19)),LAMBDA(v,i,v&IF(x=INDEX(B7:C19,i,1),INDEX(B7:C19,i,2),"")))))
E27:K31E27=DAY(E7#)&E19#
Dynamic array formulas.
 
The functions.
T_EV() tool lambda for building events expanded dates-icons relationship (all steps of Concept 1)
It is a lambda free argument, dedicated exclusively to Cat, Ev tables inputs.
Excel Formula: 
=LAMBDA(
    LET(a,SORT(FILTER(Ev,Ev[days]<>0)),r,ROWS(a),d,INDEX(a,,1),t,INDEX(a,,2),e,INDEX(a,,3),c,MAX(t),
       s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,x,INDEX(MOD(SEQUENCE(r,c)-1,c)+1<=t,q,m),
       FILTER(IF({1,0},INDEX(d,q)+m-1,VLOOKUP(INDEX(e,q),Cat,2,0)),x)
    )
)
ACALENDAR 2022.xlsx
ABCDEFGHIJKLMN
1T_EV() function
2copies of Cat and Ev tables, for easy visualizing purposeall dates expanded, ascending order
3with their icon correspondence
4=Cat[#All]=Ev[#All]=T_EV()
5categoryiconstartdayseventnotes25-12-21⏸️
6to do✅17-01-221meetingCEO meeting, 2021's report26-12-21⏸️
7important?15-08-2214vacationvacation France27-12-21⏸️
8reminder⏰11-07-221deadlineplanning vacation, booking hotel26-12-21?
9deadline⌛25-12-213day offXmas31-12-21⏸️
10meeting?26-12-211othercall mom01-01-22⏸️
11travel?31-12-215day offNew Year 02-01-22⏸️
12day off⏸️04-01-221reminderrenew fitness subscription03-01-22⏸️
13vacation?04-01-221importanttake laptop to work04-01-22⏸️
14other?15-01-223to doprepare report's dashboard04-01-22⏰
1515-01-221deadlinefinish report formulas04-01-22?
1623-08-224traveltrip to London15-01-22✅
1702-08-221reminderplanning trip to London, book flight16-01-22✅
1826-01-223meetingdepartments meetings, 2022 budget17-01-22✅
1927-01-221importantbring financial report 202115-01-22⌛
2017-01-22?
2126-01-22?
2227-01-22?
2328-01-22?
2427-01-22?
2511-07-22⌛
2602-08-22⏰
2715-08-22?
2816-08-22?
2917-08-22?
3018-08-22?
3119-08-22?
3220-08-22?
3321-08-22?
3422-08-22?
3523-08-22?
3624-08-22?
3725-08-22?
3826-08-22?
3927-08-22?
4028-08-22?
4123-08-22?
4224-08-22?
4325-08-22?
4426-08-22?
45
ACALENDAR 4
Cell Formulas
RangeFormula
B4,J4,E4B4=FORMULATEXT(B5)
B5:C14B5=Cat[#All]
E5:H19E5=Ev[#All]
J5:K44J5=T_EV()
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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