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

Board Regular
Joined
Mar 8, 2021
Messages
88
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
4holidays9726
5other9762dmyholidaysother
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

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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
4holidays9726
5other9762dmyholidaysother
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.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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.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 ☢9726CF form 2 =ISNUMBER(SEARCH(UNICHAR(9726),B3))
817 ◾181920 ☢2122239762CF 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
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
This is how it looks on spreadsheet (screen capture)
CF pic 2.jpg
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
88
Office Version
  1. 365
Platform
  1. Windows
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) )
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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
Top