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

2015

In H7 put your month.

Jan

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

Copy B12 down to B16

In C12 put the following 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 unformatted table looks as follows.

Now just format the Excel calendar to your liking.

If you don't have time to go through the above here is the Excel Calendar.