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
832
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
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
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQRSTU
1T_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-22
408-12-2109-12-2110-12-2111-12-2112-12-2113-12-2114-12-2104-01-2205-01-2206-01-22
515-12-2116-12-2117-12-2118-12-2119-12-2120-12-2121-12-2107-01-2208-01-2209-01-22
622-12-2123-12-2124-12-2125-12-2126-12-2127-12-2128-12-21⏸️⏸️?⏸️10-01-2211-01-2212-01-22
729-12-2130-12-2131-12-2101-01-2202-01-2203-01-2204-01-22⏸️⏸️⏸️⏸️⏸️⏰?13-01-2214-01-2215-01-22
805-01-2206-01-2207-01-2208-01-2209-01-2210-01-2211-01-2216-01-2217-01-2218-01-22
912-01-2213-01-2214-01-2215-01-2216-01-2217-01-2218-01-22✅⌛✅✅?19-01-2220-01-2221-01-22
1019-01-2220-01-2221-01-2222-01-2223-01-2224-01-2225-01-2222-01-2223-01-2224-01-22
1126-01-2227-01-2228-01-2229-01-2230-01-2231-01-2201-02-22????25-01-2226-01-2227-01-22
1202-02-2203-02-2204-02-2205-02-2206-02-2207-02-2208-02-2228-01-2229-01-2230-01-22
1309-02-2210-02-2211-02-2212-02-2213-02-2214-02-2215-02-2231-01-2201-02-2202-02-22
1416-02-2217-02-2218-02-2219-02-2220-02-2221-02-2222-02-22
1523-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-22
1923-03-2224-03-2225-03-2226-03-2227-03-2228-03-2229-03-22
2030-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-22
2320-04-2221-04-2222-04-2223-04-2224-04-2225-04-2226-04-22
2427-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-22
2718-05-2219-05-2220-05-2221-05-2222-05-2223-05-2224-05-22
2825-05-2226-05-2227-05-2228-05-2229-05-2230-05-2231-05-22
2901-06-2202-06-2203-06-2204-06-2205-06-2206-06-2207-06-22
3008-06-2209-06-2210-06-2211-06-2212-06-2213-06-2214-06-22
3115-06-2216-06-2217-06-2218-06-2219-06-2220-06-2221-06-22
3222-06-2223-06-2224-06-2225-06-2226-06-2227-06-2228-06-22
3329-06-2230-06-2201-07-2202-07-2203-07-2204-07-2205-07-22
3406-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-22
3620-07-2221-07-2222-07-2223-07-2224-07-2225-07-2226-07-22
3727-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-22
3910-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-22
43
ACALENDAR 5
Cell Formulas
RangeFormula
B2,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.
 
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
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQR
1ACALENDAR([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] Jan2022
5categoryiconstartdayseventnotesMonTueWedThuFriSatSun
6to do✅17-01-221meetingCEO meeting, 2021's report⏸️⏸️01⏸️02⏸️
7important?15-08-2214vacationvacation France03⏸️04⏸️⏰?* 0506070809
8reminder⏰11-07-221deadlineplanning vacation, booking hotel101112131415✅⌛16✅
9deadline⌛25-12-213day offXmas17✅?181920212223
10meeting?26-12-211othercall mom242526?27??28?2930
11travel?31-12-215day offNew Year 31
12day off⏸️04-01-221reminderrenew fitness subscription
13vacation?04-01-221importanttake laptop to workNote: function will set a "*" to the left of day value "dy"
14other?15-01-223to doprepare report's dashboard
1515-01-221deadlinefinish report formulasdy,omitted,mn,12,yr,2021
1623-08-224traveltrip to London=ACALENDAR(,12,2021)
1702-08-221reminderplanning trip to London, book flight Dec2021
1826-01-223meetingdepartments meetings, 2022 budgetMonTueWedThuFriSatSun
1927-01-221importantbring financial report 202101020304* 05
2006070809101112
2113141516171819
22202122232425⏸️26⏸️?
2327⏸️28293031⏸️⏸️⏸️
24⏸️⏸️⏰?
25
26Note: 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.
29
30dy,omitted,mn,8,yr,omitted
31=ACALENDAR(,8)
32 Aug2022
33MonTueWedThuFriSatSun
340102⏰0304* 050607
3508091011121314
3615?16?17?18?19?20?21?
3722?23??24??25??26??27?28?
38293031
39
40
41any date dy,23,mn,11,yr,2020
42=ACALENDAR(23,11,2020)
43 Nov2020
44MonTueWedThuFriSatSun
4501
4602030405060708
4709101112131415
4816171819202122
49* 23242526272829
5030
51
ACALENDAR 6
Cell Formulas
RangeFormula
J3,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.
 
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.
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQ
1AGENDA bonus function. Concept
2
3Unfolding the function:
4
5t,TODAY() t, today's date value
6s,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 dates
9 *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 REDUCE
10 *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 REDUCE
11r,ROWS(a)+1,q,SEQUENCE(r)-1 adding a row to rows dimension of "a" , for adding of headers
12SWITCH(q,0,Ev[#Headers],INDEX(a,q,SEQUENCE(,4))) final result, headers row added on top of "a"
13
ACALENDAR 7
 
ACALENDAR 2022.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1AGENDA. The function
2copies of Cat and Ev tables, for easy visualizing purpose
3=Cat[#All]=IF(Ev[#All]="","",Ev[#All])=ACALENDAR()all arg. omitted, filter all events of current month
4categoryiconstartdayseventnotes Jan2022=AGENDA()
5to do✅17-01-221meetingCEO meeting, 2021's reportMonTueWedThuFriSatSunstartdayseventnotes
6important?15-08-2214vacationvacation France⏸️⏸️01⏸️02⏸️04-01-220reminderrenew fitness subscription
7reminder⏰11-07-221deadlineplanning vacation, booking hotel03⏸️04⏸️?05* 0607080904-01-221importanttake laptop to work
8deadline⌛25-12-213day offXmas101112131415✅⌛16✅15-01-223to doprepare report's dashboard
9meeting?26-12-21othercall mom17✅?18192021222315-01-221deadlinefinish report formulas
10travel?31-12-215day offNew Year 242526?27??28?293017-01-221meetingCEO meeting, 2021's report
11day off⏸️04-01-22reminderrenew fitness subscription3126-01-223meetingdepartments meetings, 2022 budget
12vacation?04-01-221importanttake laptop to work27-01-221importantbring financial report 2021
13other?15-01-223to doprepare report's dashboardNote: events that have "blank" duration days do not show
1415-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 flightstartdayseventnotes
1726-01-223meetingdepartments meetings, 2022 budget04-01-221importanttake laptop to work
1827-01-221importantbring financial report 202117-01-221meetingCEO meeting, 2021's report
1926-01-223meetingdepartments meetings, 2022 budget
20filter all events btwn dates27-01-221importantbring financial report 2021
21=AGENDA("3-1-22","23-1-22")
22startdayseventnotesfilter all events btwn 1-dec-21 and end of current month
2304-01-220reminderrenew fitness subscription=AGENDA("1-dec-21")
2404-01-221importanttake laptop to workstartdayseventnotes
2515-01-223to doprepare report's dashboard25-12-213day offXmas
2615-01-221deadlinefinish report formulas26-12-210othercall mom
2717-01-221meetingCEO meeting, 2021's report31-12-215day offNew Year
2804-01-220reminderrenew fitness subscription
29Note: As we see above, AGENDA returns all events, even the ones with no duration04-01-221importanttake laptop to work
3015-01-223to doprepare report's dashboard
31filter events btwn dates, evnt clm contains "vac"15-01-221deadlinefinish report formulas
32=AGENDA("1-1-22","1-sep-22","vac")17-01-221meetingCEO meeting, 2021's report
33startdayseventnotes26-01-223meetingdepartments meetings, 2022 budget
3415-08-2214vacationvacation France27-01-221importantbring financial report 2021
35
36filter 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"})
38startdayseventnotesstartdayseventnotes
3915-01-221deadlinefinish report formulas15-01-223to doprepare report's dashboard
4027-01-221importantbring financial report 202115-01-221deadlinefinish report formulas
4111-07-221deadlineplanning vacation, booking hotel17-01-221meetingCEO meeting, 2021's report
4227-01-221importantbring financial report 2021
43if we try to filter for something that has no results11-07-221deadlineplanning vacation, booking hotel
44=AGENDA(,,"xyz")02-08-221reminderplanning trip to London, book flight
45startdayseventnotes
46not found#REF!#REF!#REF!
47
ACALENDAR 8
Cell Formulas
RangeFormula
E3,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 Formatting
CellConditionCell FormatStop If True
J6:P11Expression=LEFT(J6)="*"textNO
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,430
Messages
6,124,853
Members
449,194
Latest member
HellScout

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