One thing I find myself doing often is coming up with a schedule of when I’m going to do something. Many times, I want to “play with the dates” to see what makes the most sense. For that, I typically create a calendar in Excel.
I say “dynamic” because you just have to put in the start date, and the rest populates on it’s own AND it shades the odd (or even) months a specific color – all automatically.
Something like the above is what I mean.
Creating a simple calendar:
To start, just create a basic grid for the day headings:
next, make the first Sunday the place where you put the real starting date. This should be a real Sunday date. Then, have the rest of the days of the week, be “one greater than the day before”. So, the formulas for each cell should be:
which should result in something like this:
Now, for the next week and ALL subsequent weeks, Sunday should be the previous Saturday, plus 1. Then, the formulas for Mon-Sat should be the same as the previous row, so you can just select and “drag” that formula down. So, week too looks something like this (with pseudo-formulas):
and like this with real data:
There are two more columns I like to add, and that is the month name on the left and right margins. The current month should just be whatever the month is for the cell right next to it. So, that looks like this:
At this point, you might think: “this is a horrible looking calculator!” – and you’re right.
The trick here is that we need to store the actual, full date in each cell – and we can use custom “formats” to display just certain parts of the date. Let’s do that now!
Let’s start with the days of the week. We just want to display the singular day. So, select columns C through I, right-click and choose Format Cells
choose “Custom” and type in “d” which will return the day number:
and voila, each cell – even though it’s storing the full date, is just showing the “day” portion:
Now let’s use the same technique for the month names on the margin (columns B and J):
and this time type “MMM” which will bring back a 3-character designation for the month:
and voila – despite those cells storing the actual date, they are formatted to just show the month of the current date:
Now. We can resize and format things a bit. To save space, I’m going to turn those month names sideways too:
and I end up with something like this:
I can now select the last row and drag it down to repeat for however many weeks I want:
I guess this is alright, but it’s kind of hard to read. So, I’ll select all the cells, and set the background to White – this will make it so there are no passive gridlines:
so then, I’ll add a grid to just the calendar:
OK, that’s better but I can’t easily distinguish between months. For that, I’m going to use “conditional formatting”. Select columns B through J – you want to say if the CURRENT cell (which is a date) is a month that is divisible by 2, then apply a background color:
and set up the rule like this:
That formula in there is this:
=MOD(MONTH(INDIRECT(ADDRESS(ROW(),COLUMN()))),2) = 0
So, the INDIRECT goes and takes the value of the current cell being evaluated, goes and gets the month from it, and then “mods” it with 2 to see if there is a remainder. If there is no remainder, then the current month is divisible by 2 (it’s an even numbered month). I click OK and voila:
At this point you can use the space to the right or left to put milestones or notes for each week. This is great, but what if you want to be able to type things in at the day level? If you do that now, it will break the whole calendar! For that, we need another row.
Creating a more-complicated calendar:
Now that we have a simple calendar working, now let’s see if we can make it so we can write in text for each day if we’d like.
We first need to add a row in-between each row. In reality, you just need to do this at the top of the calendar, because you can select, and then drag your changes down later. So, you might start with something like this:
Let’s resize the partner cells (like D3 and D4) so that they together, make a square, and let’s join month cells (with “Merge and Center”):
This means that we can write things in the cells for row 4, 6, and 8 for example, without messing up the “date”. However, we lost our coloring. We can fix that by adding another conditional formatting formula. This one will be for just the weekdays, and it’s going to be the same, except we are going to check the value in ROW-1.
In the case of a real date cell, row-1 won’t be a valid date, so it won’t alter the coloring. In the case of our text cell, the row-1 WILL be the date to which we are associated, so it will share formatting:
=MOD(MONTH(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),2) = 0
And that gives us a dynamic calendar that we can write in, at a daily-level:
As an added bonus, we can also add some highlighting for special keywords if you’d like:
and that will highlight the text portion of the cell:
I find these to be useful when I’m planning a project. I’ve also mentioned these many times with people, so I thought I’d document it and share a sample. You can download an .xlsx file with both calendars in it from here: