How to create a dynamic calendar in MS Excel

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.

image

Something like the above is what I mean.

Creating a simple calendar:
To start, just create a basic grid for the day headings:

image

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:

image

which should result in something like this:

image

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):

image

and like this with real data:

image

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:
image

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

image

choose “Custom” and type in “d” which will return the day number:

image

and voila, each cell – even though it’s storing the full date, is just showing the “day” portion:

image

Now let’s use the same technique for the month names on the margin (columns B and J):

image

and this time type “MMM” which will bring back a 3-character designation for the month:

image

and voila – despite those cells storing the actual date, they are formatted to just show the month of the current date:

image

Now. We can resize and format things a bit. To save space, I’m going to turn those month names sideways too:

image

and I end up with something like this:

image

I can now select the last row and drag it down to repeat for however many weeks I want:

image

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:

image

so then, I’ll add a grid to just the calendar:

image

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:

image

and set up the rule like this:

image

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:

image

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:

image

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”):

image

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:

image

As an added bonus, we can also add some highlighting for special keywords if you’d like:

image

and that will highlight the text portion of the cell:

image

Summary:
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:

Download Here

Posted in Computers and Internet, General, Organization will set you free, Professional Development, Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2 other followers

%d bloggers like this: