Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excel One formula One calendar
One formula One calendar by ltminh98
--For Excel 365 only--
Insert year in cell A1. For example 2020.
Code:
=IFERROR(TRANSPOSE(CHOOSE(SEQUENCE(1,13,1,1),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,"\/",TEXT(DATE($A$1,1,1)-WEEKDAY(DATE($A$1,1,1),1)+ROW(1:42),"DDD")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,1,1),"MMMM"),TEXT(DATE($A$1,1,1)-WEEKDAY(DATE($A$1,1,1))+ROW(1:42),"[<"&DATE($A$1,1,1)&"] ;[>"&EOMONTH(DATE($A$1,1,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,2,1),"MMMM"),TEXT(DATE($A$1,2,1)-WEEKDAY(DATE($A$1,2,1))+ROW(1:42),"[<"&DATE($A$1,2,1)&"] ;[>"&EOMONTH(DATE($A$1,2,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,3,1),"MMMM"),TEXT(DATE($A$1,3,1)-WEEKDAY(DATE($A$1,3,1))+ROW(1:42),"[<"&DATE($A$1,3,1)&"] ;[>"&EOMONTH(DATE($A$1,3,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,4,1),"MMMM"),TEXT(DATE($A$1,4,1)-WEEKDAY(DATE($A$1,4,1))+ROW(1:42),"[<"&DATE($A$1,4,1)&"] ;[>"&EOMONTH(DATE($A$1,4,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,5,1),"MMMM"),TEXT(DATE($A$1,5,1)-WEEKDAY(DATE($A$1,5,1))+ROW(1:42),"[<"&DATE($A$1,5,1)&"] ;[>"&EOMONTH(DATE($A$1,5,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,6,1),"MMMM"),TEXT(DATE($A$1,6,1)-WEEKDAY(DATE($A$1,6,1))+ROW(1:42),"[<"&DATE($A$1,6,1)&"] ;[>"&EOMONTH(DATE($A$1,6,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,7,1),"MMMM"),TEXT(DATE($A$1,7,1)-WEEKDAY(DATE($A$1,7,1))+ROW(1:42),"[<"&DATE($A$1,7,1)&"] ;[>"&EOMONTH(DATE($A$1,7,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,8,1),"MMMM"),TEXT(DATE($A$1,8,1)-WEEKDAY(DATE($A$1,8,1))+ROW(1:42),"[<"&DATE($A$1,8,1)&"] ;[>"&EOMONTH(DATE($A$1,8,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,9,1),"MMMM"),TEXT(DATE($A$1,9,1)-WEEKDAY(DATE($A$1,9,1))+ROW(1:42),"[<"&DATE($A$1,9,1)&"] ;[>"&EOMONTH(DATE($A$1,9,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,10,1),"MMMM"),TEXT(DATE($A$1,10,1)-WEEKDAY(DATE($A$1,10,1))+ROW(1:42),"[<"&DATE($A$1,10,1)&"] ;[>"&EOMONTH(DATE($A$1,10,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,11,1),"MMMM"),TEXT(DATE($A$1,11,1)-WEEKDAY(DATE($A$1,11,1))+ROW(1:42),"[<"&DATE($A$1,11,1)&"] ;[>"&EOMONTH(DATE($A$1,11,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TEXT(DATE($A$1,12,1),"MMMM"),TEXT(DATE($A$1,12,1)-WEEKDAY(DATE($A$1,12,1))+ROW(1:42),"[<"&DATE($A$1,12,1)&"] ;[>"&EOMONTH(DATE($A$1,12,1),0)&"] ;dd")),",","</b><b>")&"</b></a>","//b"))),"")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment