Calendar in Excel
The following article takes you through how to create a calendar with Excel. The calendar will be able to show both dates for month and year on a single page and will be flexible enough to show a great many years (000s). There is a file attached to show the completed Excel calendar.
The Excel calendar will look very similar to the above once you are finished.
Firstly in F7 put your year
2020
In H7 put your month.
Nov
In B9 put the following formula;
=DATE(F7,MONTH(DATEVALUE("01" &H7)),1)
In B10-H10 put the days for the week starting with which ever day you wish. I started with Monday.
If you started your dates with a Monday in B10 put the following formula;
=B9-(WEEKDAY(B9,2)-1)
Note If you started the calendar with a Sunday this formula;
=B9-(WEEKDAY(B9,1)-1)
This is important as this formula will drive the calendar and the 1 in the weekday calendar is for Sunday (2 therefore is for Monday etc.)
In B11 put this formula
=B11+1
drag this across to H11.
In B12 ensure you refer to H11
=B11+1
Copy B12 down to B16
In C12 put the following B12+1 and copy it down and across to H16
Highlight the area from B11 to H16
Right click and choose Format Cells.
From the menu choose Custom format and type D
After you have chosen your format your calendar should look like the following;
Highlight from B11:H16 and choose conditional formatting.
New Rule - Use Formula to determine which cells to format.
In the formula bar put the following formula;
=MONTH(B11)<>MONTH($B$9)
Now choose a colour fill colour and a font colour (note make sure these two colours are identical).
The formated table looks as follows.
If you don't have time to go through the above here is the Excel Calendar.
