# ACALENDAR  	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 (Mar 18, 2021)

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.

```
=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.xlsmABCDEFGHIJKLMNO1icons unichars values used in formula2these can be changed inside the formula, are defined at the beginning of LET3today?1281974holidays◾97265other☢9762dmyholidaysother6all values set26march2107-03-2101-03-217=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)14-03-2123-03-218 Mar202121-03-2110-03-219MonTueWedThuFriSatSun28-03-2120-03-211001 ☢020304050607 ◾25-03-2111080910 ☢11121314 ◾12151617181920 ☢21 ◾132223 ☢2425 ◾26 ?2728 ◾14293031151617everything ignored, displays todays day month,year,no icons for holydays and other dates18=ACALENDAR(,,,,)19 Mar202120MonTueWedThuFriSatSun210102030405060722080910111213142315161718 ?192021242223242526272825293031262728if,day or month, or year are ignored, formula chooses curent day,month,or year29if we want to display same day and month of today() but of next year30=ACALENDAR(,,22,J32:J34,K32:K35)31 Mar2022holidays 22other 2232MonTueWedThuFriSatSun06-03-2215-03-2233010203040506 ◾13-03-2231-03-223407080910111213 ◾27-03-2204-04-22351415 ☢161718 ?192010-04-223621222324252627 ◾3728293031 ☢383940default use, day,month and year ignored , but the holydays and other dates selected41will always show the todays day and curent holidays and other dates coresponding to the curent month42place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly43want to check future events, change the date and month in the formula44=ACALENDAR(,,,J6:J10,K6:K9)45 Mar202146MonTueWedThuFriSatSun4701 ☢020304050607 ◾48080910 ☢11121314 ◾4915161718 ?1920 ☢21 ◾502223 ☢2425 ◾262728 ◾512930315253ACALENDAR postCell FormulasRangeFormulaC3: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.


----------



## Xlambda (Mar 19, 2021)

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.

```

```
LAMBDA 5.0.xlsmABCDEFGHIJKLMN1icons unichars values used in formula2these can be changed inside the formula, are defined at the beginning of LET3today?1281974holidays◾97265other☢9762dmyholidaysother6all values set26march2107-03-2101-03-217=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)14-03-2123-03-218 Mar202121-03-2110-03-219MonTueWedThuFriSatSun28-03-2120-03-211001 ☢020304050607 ◾25-03-2111080910 ☢11121314 ◾12151617181920 ☢21 ◾132223 ☢2425 ◾26 ?2728 ◾14293031151617everything ignored, displays todays day month,year,no icons for holydays and other dates18=ACALENDAR(,,,,)19 Mar202120MonTueWedThuFriSatSun21010203040506072208091011121314231516171819 ?2021242223242526272825293031262728if,day or month, or year are ignored, formula chooses curent day,month,or year29if we want to display same day and month of today() but of next year30=ACALENDAR(,,22,J32:J34,K32:K35)31 Mar2022holidays 22other 2232MonTueWedThuFriSatSun06-03-2215-03-2233010203040506 ◾13-03-2231-03-223407080910111213 ◾27-03-2204-04-22351415 ☢16171819 ?2010-04-223621222324252627 ◾3728293031 ☢383940default use, day,month and year ignored , but the holydays and other dates selected41will always show the todays day and curent holidays and other dates coresponding to the curent month42place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly43want to check future events, change the date and month in the formula44=ACALENDAR(,,,J6:J10,K6:K9)45 Mar202146MonTueWedThuFriSatSun4701 ☢020304050607 ◾48080910 ☢11121314 ◾491516171819 ?20 ☢21 ◾502223 ☢2425 ◾262728 ◾512930315253ACALENDAR postCell FormulasRangeFormulaC3: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.


----------



## schardt679 (May 3, 2021)

This is a great one as usual. How do you get the colored calendar icon?


----------



## Xlambda (May 4, 2021)

schardt679 said:


> This is a great one as usual. How do you get the colored calendar icon?


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.xlsxABCDEFGHIJKLMNO1in cell B3=ACALENDAR(,,,B13:B15,C13:C16)23 May20214MonTueWedThuFriSatSunIconsCF formulas50102 ☢unicode=UNICHAR(J6:J8)range B3:H1060304 ?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 ◾293010311112holidaysother1317-05-2102-05-211407-05-2116-05-211528-05-2126-05-211620-05-2117Sheet11Cell FormulasRangeFormulaB1B1=FORMULATEXT(B3)B3:H10B3=ACALENDAR(,,,B13:B15,C13:C16)K5K5=FORMULATEXT(K6)K6:K8K6=UNICHAR(J6:J8)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:H10Expression=ISNUMBER(SEARCH(UNICHAR(9762),B3))textNOB3:H10Expression=ISNUMBER(SEARCH(UNICHAR(9726),B3))textNOB3:H10Expression=ISNUMBER(SEARCH(UNICHAR(128197),B3))textNO


----------



## Xlambda (May 5, 2021)

This is how it looks on spreadsheet (screen capture)


----------



## Xlambda (May 5, 2021)

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:



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) )


----------



## Xlambda (Jan 5, 2022)

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.xlsxABCDEFGHIJKLM1Input tables set up.2events category table (Cat)events table (Ev)icons unicode values3Ev sample-imagine that this was filed in during dec 2021unicodeunicharlength4categoryiconstartdayseventnotes9989✅15to do✅17-01-221meetingCEO meeting, 2021's report128680?26important?15-08-2214vacationvacation France9200⏰17reminder⏰11-07-221deadlineplanning vacation, booking hotel8987⌛18deadline⌛25-12-213day offXmas128101?29meeting?26-12-211othercall mom128747?210travel?31-12-215day offNew Year 9208⏸111day off⏸️04-01-221reminderrenew fitness subscription127958?212vacation?04-01-221importanttake laptop to work128681?213other?15-01-223to doprepare report's dashboard1415-01-221deadlinefinish report formulas128221?215other categories examples23-08-224traveltrip to London9742☎116that can be added anytime02-08-221reminderplanning trip to London, book flight128231?21726-01-223meetingdepartments meetings, 2022 budget128279?218memo?27-01-221importantbring financial report 2021128161?219call☎↑↑↑128269?220email?↑↑clm with DV =Cat[category]128204?221link?dates in any order, different days duration, same date, more events128295?222idea?10002✒123research?Notes:128176?224remember?- both tables names and headers should be defined as such127384?225maintenance?- days clm represent duration of the event including starting date127873?226contract✒- start dates can be in any order (filter table buttons can be used for sorting)127947?227paycheck?- event clm has Data Validation list=Cat[category]128138?228safety?- if we want an event not to be displayed by ACALENDAR function, 9971⛳129birthdate?but still to be part of "Ev" table, we can delete its duration days (blank),127942?230workout?and only if we want to permanently delete an event, we can delete the table row127881?231medicine?32sport⛳33tournament?34party?35ACALENDAR 1Cell FormulasRangeFormulaJ4: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 ValidationCellAllowCriteriaG5:G18List=$B$5:$B$13


----------



## Xlambda (Jan 5, 2022)

ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ1New 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-121st 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#-13Start 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-22meetingTRUEfca810-01-223important09-01-22?445692meeting↓=QUOTIENT(M10#-1,4)+1123409-01-22meetingTRUEstep 7. vlookup events icons 2nd array912-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?ia1108-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?42ACALENDAR 2Cell FormulasRangeFormulaZ2Z2=FORMULATEXT(Z7)AB4,M7AB4=FORMULATEXT(AB7)AD6,F28,T14,AF9,Q9,T7,I7AD6=FORMULATEXT(AD7)Z7:Z26Z7=INDEX(I8:I12,O10#)+Q10#-1AB7: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)+1M10:M29M10=SEQUENCE(5*4)O10:O29O10=QUOTIENT(M10#-1,4)+1Q10:Q29Q10=MOD(M10#-1,4)+1AF10:AF29AF10=VLOOKUP(AB7#,B19:C27,2,0)T15:W19T15=T8#<=J8:J12F29:G41F29=FILTER(IF({1,0},Z7#,AF10#),AD7#)Dynamic array formulas.


----------



## Xlambda (Jan 5, 2022)

ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQ1New ACALENDAR. Concept 2. Appending corresponding icons of corresponding events to a dates array23dates that have more than 1 icon (more events/same day)45array from previous post6a=SEQUENCE(5,7,"1-1-22")b708-01-22?01-01-2202-01-2203-01-2204-01-2205-01-2206-01-2207-01-22809-01-22?08-01-2209-01-2210-01-2211-01-2212-01-2213-01-2214-01-22909-01-22✅15-01-2216-01-2217-01-2218-01-2219-01-2220-01-2221-01-221010-01-22✅22-01-2223-01-2224-01-2225-01-2226-01-2227-01-2228-01-221111-01-22✅29-01-2230-01-2231-01-2201-02-2202-02-2203-02-2204-02-221210-01-22?1311-01-22?- will use a nested MAP(REDUCE)) construction1412-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???2223c2425 - appending the results =DAY(b)&c26=DAY(E7#)&E19#271234567288?9?✅10✅?11✅?12?⌛1314?2915?16?17?1819202130222324252627283129303112343233Note: Dates array in this example is not offset acording to weekday, ACALENDAR will take care of that34ACALENDAR 3Cell FormulasRangeFormulaE6,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.


----------



## Xlambda (Jan 5, 2022)

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.

```
=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.xlsxABCDEFGHIJKLMN1T_EV() function2copies of Cat and Ev tables, for easy visualizing purposeall dates expanded, ascending order3with their icon correspondence4=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?45ACALENDAR 4Cell FormulasRangeFormulaB4,J4,E4B4=FORMULATEXT(B5)B5:C14B5=Cat[#All]E5:H19E5=Ev[#All]J5:K44J5=T_EV()Dynamic array formulas.


----------



## Xlambda (Mar 18, 2021)

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.

```
=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.xlsmABCDEFGHIJKLMNO1icons unichars values used in formula2these can be changed inside the formula, are defined at the beginning of LET3today?1281974holidays◾97265other☢9762dmyholidaysother6all values set26march2107-03-2101-03-217=ACALENDAR(G6,H6,I6,J6:J10,K6:K9)14-03-2123-03-218 Mar202121-03-2110-03-219MonTueWedThuFriSatSun28-03-2120-03-211001 ☢020304050607 ◾25-03-2111080910 ☢11121314 ◾12151617181920 ☢21 ◾132223 ☢2425 ◾26 ?2728 ◾14293031151617everything ignored, displays todays day month,year,no icons for holydays and other dates18=ACALENDAR(,,,,)19 Mar202120MonTueWedThuFriSatSun210102030405060722080910111213142315161718 ?192021242223242526272825293031262728if,day or month, or year are ignored, formula chooses curent day,month,or year29if we want to display same day and month of today() but of next year30=ACALENDAR(,,22,J32:J34,K32:K35)31 Mar2022holidays 22other 2232MonTueWedThuFriSatSun06-03-2215-03-2233010203040506 ◾13-03-2231-03-223407080910111213 ◾27-03-2204-04-22351415 ☢161718 ?192010-04-223621222324252627 ◾3728293031 ☢383940default use, day,month and year ignored , but the holydays and other dates selected41will always show the todays day and curent holidays and other dates coresponding to the curent month42place the calendar on the daily spreadsheet and leave it there, choose a format you like, updates dynamicaly43want to check future events, change the date and month in the formula44=ACALENDAR(,,,J6:J10,K6:K9)45 Mar202146MonTueWedThuFriSatSun4701 ☢020304050607 ◾48080910 ☢11121314 ◾4915161718 ?1920 ☢21 ◾502223 ☢2425 ◾262728 ◾512930315253ACALENDAR postCell FormulasRangeFormulaC3: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.


----------



## Xlambda (Jan 5, 2022)

The functions.
*T_IC(a*) tool lambda, appends all icons of T_EV to their correspondent dates found on dates array "a", keeping the frame structure of "a". (*Concept 2*)
Calls *T_EV*.
*a: dates array argument*

```
=LAMBDA(a,LET(e,T_EV(),MAP(a,LAMBDA(x,REDUCE("",SEQUENCE(ROWS(e)),LAMBDA(v,i,v&IF(x=INDEX(e,i,1),INDEX(e,i,2),"")))))))
```
This function will be called by ACALENDAR, "a" array will be calculated by ACALENDAR as one of its variables, and it will be offset for proper matching of weekdays.
Even if ACALENDAR will never fed an array like in our examples, here is what T_IC returns for a larger array of dates.
ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQRSTU1T_IC(a)"a" dates from 1-12-21 to Sep 2022note dates that have more than 1 icon/datedifferent frame structure of "a"2=SEQUENCE(40,7,"1-12-21")a=T_IC(B3#)=SEQUENCE(11,3,"1-1-22")301-12-2102-12-2103-12-2104-12-2105-12-2106-12-2107-12-21 01-01-2202-01-2203-01-22408-12-2109-12-2110-12-2111-12-2112-12-2113-12-2114-12-2104-01-2205-01-2206-01-22515-12-2116-12-2117-12-2118-12-2119-12-2120-12-2121-12-2107-01-2208-01-2209-01-22622-12-2123-12-2124-12-2125-12-2126-12-2127-12-2128-12-21⏸️⏸️?⏸️10-01-2211-01-2212-01-22729-12-2130-12-2131-12-2101-01-2202-01-2203-01-2204-01-22⏸️⏸️⏸️⏸️⏸️⏰?13-01-2214-01-2215-01-22805-01-2206-01-2207-01-2208-01-2209-01-2210-01-2211-01-2216-01-2217-01-2218-01-22912-01-2213-01-2214-01-2215-01-2216-01-2217-01-2218-01-22✅⌛✅✅?19-01-2220-01-2221-01-221019-01-2220-01-2221-01-2222-01-2223-01-2224-01-2225-01-2222-01-2223-01-2224-01-221126-01-2227-01-2228-01-2229-01-2230-01-2231-01-2201-02-22????25-01-2226-01-2227-01-221202-02-2203-02-2204-02-2205-02-2206-02-2207-02-2208-02-2228-01-2229-01-2230-01-221309-02-2210-02-2211-02-2212-02-2213-02-2214-02-2215-02-2231-01-2201-02-2202-02-221416-02-2217-02-2218-02-2219-02-2220-02-2221-02-2222-02-221523-02-2224-02-2225-02-2226-02-2227-02-2228-02-2201-03-22=T_IC(R3#)1602-03-2203-03-2204-03-2205-03-2206-03-2207-03-2208-03-22⏸️⏸️⏸️1709-03-2210-03-2211-03-2212-03-2213-03-2214-03-2215-03-22⏸️⏰?1816-03-2217-03-2218-03-2219-03-2220-03-2221-03-2222-03-221923-03-2224-03-2225-03-2226-03-2227-03-2228-03-2229-03-222030-03-2231-03-2201-04-2202-04-2203-04-2204-04-2205-04-22✅⌛2106-04-2207-04-2208-04-2209-04-2210-04-2211-04-2212-04-22✅✅?2213-04-2214-04-2215-04-2216-04-2217-04-2218-04-2219-04-222320-04-2221-04-2222-04-2223-04-2224-04-2225-04-2226-04-222427-04-2228-04-2229-04-2230-04-2201-05-2202-05-2203-05-22???2504-05-2205-05-2206-05-2207-05-2208-05-2209-05-2210-05-22?2611-05-2212-05-2213-05-2214-05-2215-05-2216-05-2217-05-222718-05-2219-05-2220-05-2221-05-2222-05-2223-05-2224-05-222825-05-2226-05-2227-05-2228-05-2229-05-2230-05-2231-05-222901-06-2202-06-2203-06-2204-06-2205-06-2206-06-2207-06-223008-06-2209-06-2210-06-2211-06-2212-06-2213-06-2214-06-223115-06-2216-06-2217-06-2218-06-2219-06-2220-06-2221-06-223222-06-2223-06-2224-06-2225-06-2226-06-2227-06-2228-06-223329-06-2230-06-2201-07-2202-07-2203-07-2204-07-2205-07-223406-07-2207-07-2208-07-2209-07-2210-07-2211-07-2212-07-22⌛3513-07-2214-07-2215-07-2216-07-2217-07-2218-07-2219-07-223620-07-2221-07-2222-07-2223-07-2224-07-2225-07-2226-07-223727-07-2228-07-2229-07-2230-07-2231-07-2201-08-2202-08-22⏰3803-08-2204-08-2205-08-2206-08-2207-08-2208-08-2209-08-223910-08-2211-08-2212-08-2213-08-2214-08-2215-08-2216-08-22??4017-08-2218-08-2219-08-2220-08-2221-08-2222-08-2223-08-22????????4124-08-2225-08-2226-08-2227-08-2228-08-2229-08-2230-08-22????????4231-08-2201-09-2202-09-2203-09-2204-09-2205-09-2206-09-2243ACALENDAR 5Cell FormulasRangeFormulaB2,R15,R2,J2B2=FORMULATEXT(B3)B3:H42B3=SEQUENCE(40,7,"1-12-21")J3:P42J3=T_IC(B3#)R3:T13R3=SEQUENCE(11,3,"1-1-22")R16:T26R16=T_IC(R3#)Dynamic array formulas.


----------



## Xlambda (Jan 5, 2022)

The functions.
*ACALENDAR([dy],[mn],[yr])* Main function. Calls *T_IC* (that calls *T_EV*) , also relays on *Cat*, *Ev* as input tables.
[dy]: day argument: if 0 or omitted dy=DAY(TODAY()) ex: 1 to 31 (30,28,29)
[mn]: month argument: if 0 or omitted mn=MONTH(TODAY()) ex: 1 to 12
[yr]: year argument: if 0 or omitted yr=YEAR(TODAY()) ex: 1999, 2001,2023

```
=LAMBDA([dy],[mn],[yr],
    LET(t,TODAY(),d,IF(dy,dy,DAY(t)),m,IF(mn,mn,MONTH(t)),y,IF(yr,yr,YEAR(t)),f,EDATE(1&"-"&m&"-"&y,0),w,WEEKDAY(f,3),a,SEQUENCE(8,7,f-w-14),
       b,IF(a=DATE(y,m,d),"* ","")&IF(MONTH(a)=m,TEXT(a,"dd"),"")&T_IC(a),
       SWITCH(SEQUENCE(8),1,CHOOSE(SEQUENCE(,7),"","",TEXT(f,"mmm"),TEXT(f,"yyyy"),"","",""),2,TEXT(a,"ddd"),b)
    )
)
```
ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQR1ACALENDAR([dy],[mn],[yr])2all omitted, always updates for TODAY()3copies of Cat and Ev tables, for easy visualizing purpose=ACALENDAR()4=Cat[#All]=Ev[#All] Jan20225categoryiconstartdayseventnotesMonTueWedThuFriSatSun6to do✅17-01-221meetingCEO meeting, 2021's report⏸️⏸️01⏸️02⏸️7important?15-08-2214vacationvacation France03⏸️04⏸️⏰?* 05060708098reminder⏰11-07-221deadlineplanning vacation, booking hotel101112131415✅⌛16✅9deadline⌛25-12-213day offXmas17✅?18192021222310meeting?26-12-211othercall mom242526?27??28?293011travel?31-12-215day offNew Year 3112day off⏸️04-01-221reminderrenew fitness subscription13vacation?04-01-221importanttake laptop to workNote: function will set a "*" to the left of day value "dy"14other?15-01-223to doprepare report's dashboard1515-01-221deadlinefinish report formulasdy,omitted,mn,12,yr,20211623-08-224traveltrip to London=ACALENDAR(,12,2021)1702-08-221reminderplanning trip to London, book flight Dec20211826-01-223meetingdepartments meetings, 2022 budgetMonTueWedThuFriSatSun1927-01-221importantbring financial report 202101020304* 052006070809101112211314151617181922202122232425⏸️26⏸️?2327⏸️28293031⏸️⏸️⏸️24⏸️⏸️⏰?2526Note: By design, I left the icons to be displayed even if they are out of current month value "mn".27They do not have day value next to them, it's like having a glimpse of what events happened before,28or what events are coming, if they fit to the frame.2930dy,omitted,mn,8,yr,omitted31=ACALENDAR(,8)32 Aug202233MonTueWedThuFriSatSun340102⏰0304* 05060735080910111213143615?16?17?18?19?20?21?3722?23??24??25??26??27?28?38293031394041any date dy,23,mn,11,yr,202042=ACALENDAR(23,11,2020)43 Nov202044MonTueWedThuFriSatSun450146020304050607084709101112131415481617181920212249* 23242526272829503051ACALENDAR 6Cell FormulasRangeFormulaJ3,J42,J31,J16,E4,B4J3=FORMULATEXT(J4)J4:P11J4=ACALENDAR()B5:C14B5=Cat[#All]E5:H19E5=Ev[#All]J17:P24J17=ACALENDAR(,12,2021)J32:P39J32=ACALENDAR(,8)J43:P50J43=ACALENDAR(23,11,2020)Dynamic array formulas.


----------



## Xlambda (Jan 5, 2022)

Bonus function.
*AGENDA([sd],[ed],[evnt],[nts])*
*[sd]*: start date: string date format: if omitted sd=first day of current month-year, otherwise ex: "3-dec-21" or "17-3-21"
*[ed]*: end date: string date format: if omitted ed=last date of current month-year, otherwise ex: "13-aug-21" or "13-8-21"
*[evnt]*: string or array of strings: {"evnt1","evnt2",…} search values for event clm, under OR boolean logic
*[nts]*: string or array of strings: {"nts1","nts2",…} search values for notes clm, under OR boolean logic
Actually, the function is a long FILTER formula with 4 "include" arguments:
*=FILTER(Ev,(start clm>=st)*(start clm<=ed)*(OR(event clm=evnt1,event clm=evnt2,…..)*(OR(notes clm=nts1,notes clm=nts2….),"not found")*
The OR iterations for all elements of "evnt"/"nts" string arrays can be done very simple with 2 REDUCE formulas.

```
=LAMBDA([st],[ed],[evnt],[nts],
     LET(t,TODAY(),s,IF(ISOMITTED(st),EOMONTH(t,-1)+1,DATEVALUE(st)),e,IF(ISOMITTED(ed),EOMONTH(t,0),DATEVALUE(ed)),
        a,SORT(FILTER(Ev,(Ev[start]>=s)*(Ev[start]<=e)
            *IF(ISOMITTED(evnt),1,REDUCE(0,evnt,LAMBDA(v,a,v+ISNUMBER(SEARCH(a,Ev[event])))))
             *IF(ISOMITTED(nts),1,REDUCE(0,nts,LAMBDA(v,a,v+ISNUMBER(SEARCH(a,Ev[notes]))))),"not found")),
      r,ROWS(a)+1,q,SEQUENCE(r)-1,
      SWITCH(q,0,Ev[#Headers],INDEX(a,q,SEQUENCE(,4)))
    )
)
```
ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQ1AGENDA bonus function. Concept23Unfolding the function:45t,TODAY() t, today's date value6s,IF(ISOMITTED(st),EOMONTH(t,-1)+1,DATEVALUE(st)) s, start date variable, if "sd" arg. omitted s=1st date value of current month, or s=DATEVALUE(sd)7e,IF(ISOMITTED(ed),EOMONTH(t,0),DATEVALUE(ed)) e, end date variable, if "ed" arg. omitted e=last date of current month, or e=DATEVALUE(ed)8a,SORT(FILTER(Ev,(Ev[start]>=s)*(Ev[start]<=e)* a, a variable for FILTER, first 2 include arguments, between dates9     *IF(ISOMITTED(evnt),1,REDUCE(0,evnt,LAMBDA(v,a,v+ISNUMBER(SEARCH(a,Ev[event])))))* first OR boolean logic iterations for all elements of "evnt" array with REDUCE10     *IF(ISOMITTED(nts),1,REDUCE(0,nts,LAMBDA(v,a,v+ISNUMBER(SEARCH(a,Ev[notes]))))),"not found")) second OR bolean logic iterations for all elements of "nts" array with REDUCE11r,ROWS(a)+1,q,SEQUENCE(r)-1 adding a row to rows dimension of "a" , for adding of headers12SWITCH(q,0,Ev[#Headers],INDEX(a,q,SEQUENCE(,4))) final result, headers row added on top of "a"13ACALENDAR 7


----------



## Xlambda (Jan 5, 2022)

ACALENDAR 2022.xlsxABCDEFGHIJKLMNOPQRSTUV1AGENDA. The function2copies of Cat and Ev tables, for easy visualizing purpose3=Cat[#All]=IF(Ev[#All]="","",Ev[#All])=ACALENDAR()all arg. omitted, filter all events of current month4categoryiconstartdayseventnotes Jan2022=AGENDA()5to do✅17-01-221meetingCEO meeting, 2021's reportMonTueWedThuFriSatSunstartdayseventnotes6important?15-08-2214vacationvacation France⏸️⏸️01⏸️02⏸️04-01-220reminderrenew fitness subscription7reminder⏰11-07-221deadlineplanning vacation, booking hotel03⏸️04⏸️?05* 0607080904-01-221importanttake laptop to work8deadline⌛25-12-213day offXmas101112131415✅⌛16✅15-01-223to doprepare report's dashboard9meeting?26-12-21othercall mom17✅?18192021222315-01-221deadlinefinish report formulas10travel?31-12-215day offNew Year 242526?27??28?293017-01-221meetingCEO meeting, 2021's report11day off⏸️04-01-22reminderrenew fitness subscription3126-01-223meetingdepartments meetings, 2022 budget12vacation?04-01-221importanttake laptop to work27-01-221importantbring financial report 202113other?15-01-223to doprepare report's dashboardNote: events that have "blank" duration days do not show1415-01-221deadlinefinish report formulasrespective iconsfilter current month, event clm contains "imp" or "meet"1523-08-224traveltrip to London=AGENDA(,,{"imp","meet"})1602-08-221reminderplanning trip to London, book flightstartdayseventnotes1726-01-223meetingdepartments meetings, 2022 budget04-01-221importanttake laptop to work1827-01-221importantbring financial report 202117-01-221meetingCEO meeting, 2021's report1926-01-223meetingdepartments meetings, 2022 budget20filter all events btwn dates27-01-221importantbring financial report 202121=AGENDA("3-1-22","23-1-22")22startdayseventnotesfilter all events btwn 1-dec-21 and end of current month2304-01-220reminderrenew fitness subscription=AGENDA("1-dec-21")2404-01-221importanttake laptop to workstartdayseventnotes2515-01-223to doprepare report's dashboard25-12-213day offXmas2615-01-221deadlinefinish report formulas26-12-210othercall mom2717-01-221meetingCEO meeting, 2021's report31-12-215day offNew Year 2804-01-220reminderrenew fitness subscription29Note: As we see above, AGENDA returns all events, even the ones with no duration04-01-221importanttake laptop to work3015-01-223to doprepare report's dashboard31filter events btwn dates, evnt clm contains "vac"15-01-221deadlinefinish report formulas32=AGENDA("1-1-22","1-sep-22","vac")17-01-221meetingCEO meeting, 2021's report33startdayseventnotes26-01-223meetingdepartments meetings, 2022 budget3415-08-2214vacationvacation France27-01-221importantbring financial report 20213536filter events btwn dates, event clm contains "deadl" or "import" and notes clm contains "rep" or "book"filter events btwn dates, notes clm contain words "rep" or "book"37=AGENDA("16-01-21","12-jul-22",{"deadl","import"},{"rep","book"})=AGENDA("1-12-21","31-12-22",,{"rep","book"})38startdayseventnotesstartdayseventnotes3915-01-221deadlinefinish report formulas15-01-223to doprepare report's dashboard4027-01-221importantbring financial report 202115-01-221deadlinefinish report formulas4111-07-221deadlineplanning vacation, booking hotel17-01-221meetingCEO meeting, 2021's report4227-01-221importantbring financial report 202143if we try to filter for something that has no results11-07-221deadlineplanning vacation, booking hotel44=AGENDA(,,"xyz")02-08-221reminderplanning trip to London, book flight45startdayseventnotes46not found#REF!#REF!#REF!47ACALENDAR 8Cell FormulasRangeFormulaE3,E44,R37,E37,E32,R23,E21,R15,R4,B3,J3E3=FORMULATEXT(E4)B4:C13B4=Cat[#All]E4:H18E4=IF(Ev[#All]="","",Ev[#All])J4:P11J4=ACALENDAR()R5:U12R5=AGENDA()R16:U20R16=AGENDA(,,{"imp","meet"})E22:H27E22=AGENDA("3-1-22","23-1-22")R24:U34R24=AGENDA("1-dec-21")E33:H34E33=AGENDA("1-1-22","1-sep-22","vac")E38:H41E38=AGENDA("16-01-21","12-jul-22",{"deadl","import"},{"rep","book"})R38:U44R38=AGENDA("1-12-21","31-12-22",,{"rep","book"})E45:H46E45=AGENDA(,,"xyz")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueJ6:P11Expression=LEFT(J6)="*"textNO


----------

