Project Plans in Excel - Adding Dates

In the previous post in this series I described how to use Conditional Formatting to create a neat and simple Gantt chart alongside a simple Excel-based project plan. In this post I’ll describe how to use dates in addition to the day numbers that were featured in the previous post. The picture alongside (right) shows the result from today's post.

As with the previous case, I’m going to describe a quick and simple method. This method also takes weekends into account as non-working days. We ended the last post with what you see alongside (left).

So, let’s begin by adding the date for day 1 into cell F1. I'm typing “22/2” to represent 22nd February). It’s not readable in the small width of the cell, so we’ll go to the Format Cells window (you can use Ctrl-1 to get there quickly) and select text orientation as 90 degrees. Then, to get the date format that we want, we’ll stay in the Format Cells window and specify a custom number format of “dd-mmm (ddd)”. If the height of row 1 doesn’t automatically increase for you, just do it manually. You should have a result like this:

Now, let’s set G2 as =F1+1 and copy the formatting from F1. This gives us the correct result for G2, but it’s not a sequence that we can ask Excel to follow because we’ll get weekends included, so we should set the formula for G2 to =IF(WEEKDAY(F1)<>6,F1+1,F1+3). In other words, if the previous day is not a Friday, just add 1 to the date; if it is Friday, add three to the date. We can copy and paste G2 across the remainder of the row:

So, with very little effort, we have a Gantt chart with day numbers and dates.

In one further posting I’ll describe how to add groups of tasks and how to summarise them.