If you need a calendar on a Microsoft Excel sheet, then you have many different ways – from painstakingly entering dates manually to connecting pop-up calendars from various add-ons and macros. Another option is to implement a universal calendar for any date using just one (though very scary out of habit) array formula.
To use it, create a blank on the sheet like this:
The date in cell B2 can be anything, only the month and year are important here. The cells in the range B3:H3 can contain the names of the days of the week in any suitable format.
Now select the range B4:H9 and enter the following formula there:
=ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)) <>МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1);» «; ДАТА(ГОД(B2);МЕСЯЦ(B2);1)- (ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +{0:1:2:3:4:5}*7+{1;2;3;4;5;6;7}-1)
In the English version it will be:
=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1)) <>MONTH(DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””, DATE(YEAR(B2),MONTH(B2),1)- (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1) +{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
Then hit combination Ctrl + Shift + Enterto enter this formula as an array formula. All selected cells must be filled with the dates of the month specified in B2:
All that remains is to polish the look by adding formatting and hiding the day in the heading B2 and the month and year in the rest of the cells using the window Format Cells (Ctrl+1):
Now, by changing the date in cell B2, we will get the correct calendar for any selected month of any year according to our formula. Almost a perpetual calendar 😉
- How to connect popup calendar to excel sheet
- Quick date and time entry with the PLEX add-on
- How Excel works with dates and times
- Quick date and time entry without separators