Need to create a project schedule? Try our Gantt Chart Template!

Showing Actual Dates vs. Planned Dates in a Gantt Chart

Vertex42 Gantt Chart Actual vs Planned Dates

◄ Return to the Gantt Chart Template Page

I was recently asked if my Gantt Chart Template could be used to compare actual dates to original project dates. The simple answer is “yes, depending on how you want to show the difference.” In this post, I’ll explain how you can currently do this and also propose a new version of the Gantt Chart Template Pro that makes it easy to see visually.

1. Keep a Backup Copy of the Original Project Plan

Keeping a backup is always a good idea, and this is the most basic approach for keeping a record of the original plan. You can view files side-by-side or compare printouts.

2. Make a Copy of the GanttChart Worksheet and Rename It

You can make a copy of the Gantt Chart worksheet within the same workbook and rename the worksheets GanttChart-Actual and GanttChart-Plan (see the image below). Of course, these worksheets will be entirely independent, but it’s an easy solution for keeping a copy of the original project plan. This still doesn’t provide a good solution for easily comparing planned and actual dates, though.

Screenshot showing separate Plan and Actual worksheets

Screenshot showing separate Plan and Actual worksheets

3. Add New Columns for Storing the Original Dates

It’s easy to insert new columns in the XLSX version of the gantt chart. Many people do that to add a budget column, and in this case you could add columns to keep track of original start and end dates.

With the new columns, you can compare dates side-by-side, but that still may not be as visual as you’d like.

4. Showing the Original Dates in the Gantt Chart

The first two approaches don’t make it easy to visually compare differences between the planned and actual dates. The third method gets us part way there, but many people have wanted to see the differences in the Gantt Chart itself.

Thanks to Excel 2007+, which provides a more powerful set of conditional formatting rules than Excel 2003, it’s possible to show the planned and actual project dates within the same chart. The screenshot below shows a proposed new feature of Gantt Chart Template Pro that provides an option for displaying original dates as a diagonal fill pattern.

UPDATE 8/6/2016 :: This feature will NOT be included in any newer versions of the gantt chart template (for example version 4.0) because this feature is not compatible with Excel Online or the mobile Excel apps.


Screenshot showing a comparison of the actual vs. original dates in the Gantt Chart

Screenshot showing a comparison of the actual vs. original dates in the Gantt Chart
(click to view larger image)

You’ll see that this new version includes two new data entry columns: Original Start and Original End. After you create your plan using the normal approach, you can copy the dates from the Start and End columns into the two new columns using Copy and Paste Special > Values.

You can hide the Original Start and Original End columns if you do not want to use them or display them. You can also turn on/off the display of the original dates in the Gantt Chart by unchecking a box.

Current customers can return to the download page and download a copy of the gantt-chart.xlsx file. It includes a new worksheet named GanttChart-BETA with this new feature highlighted. Note that this is only an update for the XLSX version of the spreadsheet. This new feature is not possible in Excel 2003 (XLS).

Please Let Me Know What You Think

I would love some feedback on this new feature, especially regarding the following questions:

A) You’ll see from the screenshot that the two new columns use conditional formatting to highlight dates green or red if the start and end dates are ahead or behind schedule, respectively. Is this conditional formatting useful or just redundant?

B) I could either include the new version as an optional worksheet (like I’m doing now) or I could make this a standard feature and hide the two new columns by default – letting the user decide to unhide them if they want to. What would you prefer?

C) There are many fill patterns that could be used to show the original dates in the gantt chart. I picked the diagonal lines because they show up well on different color bars, but if you find something that you think works better, please let me know.

D) At the moment, the diagonal lines are only shown if the original dates are different than the current dates. See Row 23 in the screenshot above to see what it looks like when there is no change in the dates. Would it be better to show the diagonal lines regardless (noting that you can turn them all off if you want to)?

UPDATE 8/6/2016 :: This feature will NOT be included in any newer versions of the gantt chart template (for example version 4.0) because this feature is not compatible with Excel Online or the mobile Excel apps.

43 comments… add one
  • Wonderful.

    Reply
  • I am getting a break in the formatting in the Gantt chart when using the monthly display but not in the weekly display

    Reply
    • @Don … not sure what you mean. Can you email a screenshot or something? See the contact page for the email address.

      Reply
  • Hi,

    The new feature sounds nice.
    I have some questions:
    1. Could you add some more color schemes?
    2. Could you make it possible to choose between entering the begin and end date of a project. The same goes for the different tasks. The user could setup the project settings in the beginning.
    3. I would also have the option not to use only the number of weeks but also the week numbers of that year/month. this is sometimes useful when talking to people. they like to know which week of the month the task will be.

    regards,

    Ger

    Reply
    • @ger … thank you for the suggestions. #1 can be done by adding more conditional formatting conditions. #2 is already possible by using different template rows. One of the demo videos shows this. #3 is also something that could be added fairly simply if you use the WEEKNUM() function to calculate the week number for a given date.

      Reply
      • Hi,
        I would like to know if there is any template where you can compare actual progress vs planned progress in construction. Could you pls guide me on this if possible..

        Thanks.

        Reply
        • @Mishal, I’m afraid that this blog article is the only documentation I have describing what you are asking about.

  • Does this app work on Mac (OSX) computers.?

    Reply
    • @hmoussly, If by “app” you mean the Gantt Chart Template … it should work in the latest versions of Excel for Mac (Excel 2008 if you use the .xls file and Excel 2011 if you use the .xlsx file).

      Reply
  • Hi, I am not sure how i can show all the months. e.g. in Gantt chart, I can only see the months Oct,Nov,Jan but I do wish to view Dec and Feb too but am not sure how i can display them.

    Reply
    • @Nira, I’d need more information (via email) to answer your question. To display a longer time span, you could select Weekly or Monthly from the drop-down list.

      Reply
  • I have the 6/9/2010 created version of gantt chart pro template with the 2.4 beta version of actual vs. planned. Is that the latest version? I check before I carry over our normal gantt chart to the new version. Looks good….

    K

    Reply
    • @KCarr, there have been a few minor changes since 2.4.0, but probably nothing major enough to warrant moving data to the newest version. However, it would be good to start new projects with the latest version.

      Reply
      • Thanks for the reply John. How do I get the newest version?

        K

        Reply
        • @Kevin, To return to the download page, you can follow these instructions. If that doesn’t work, send me an email, and I’ll send you the file(s).

  • As for the requested feedback. Definitely do number B where that it is within the “base chart”. Huge amount of work to carry over a std chart to the new one….

    Others are good for my use.

    K

    Reply
  • I like the GanttChart Beta. Here are a couple tips that I think would be helpful

    1. Original dates could be links to Gantt chart tab Start/End cells
    2. I would like to have a button that when pressed hides all sub-tasks rows so that I can print a summary of just task categories

    Great work, I think this is a great tool!

    Reply
  • It is very convinient to spread and share project targets and status within a diverse team. Work efficiency is approved to be higher.

    Reply
  • There seems to be a problem with the locked formula that creates the oblique hatching in line 13. The hatching continues all the way to the right no matter what values show up in the controlling cells.
    The rest seems to work great and after a small learning curve I am quite pleased.
    Thanks

    Reply
    • @Ken, I’m afraid I am unable to reproduce what you are seeing. For me (using Excel 2010), the spreadsheet is behaving correctly (as shown in the screenshots in this post).

      Reply
      • I’m having the same error that Ken Dewar was experiencing. How was this resolved?

        Reply
        • @Dave, I don’t think I ever got a follow-up from Ken that allowed me to reproduce the error. Emailing me your file would be required for me to diagnose the problem further, as all of the files that I have work fine.

  • I hate to appear stupid but I would like to use the beta version and cannot figure out how to get a clean template???

    Reply
    • @Ted, I’m not sure what you mean by “clean” template. For assistance, please email me using the email address listed on the about/contact page.

      Reply
  • I have just recently begun to use the Gantt Chart Pro, Beta version. In answer to your queries above:
    A. I really like the conditional formatting. Being able to see at a quick glance whether one is ahead or lagging on a given task is of great value.
    B. Since I began use on the Beta version, I have been using it exclusively although I admit that I do miss the “Days done” and “Days left” on the original gantt chart.
    C. I like the fill patterns as they are at present.
    D. I like the current arrangement of the diagonal lines when they only show up if the original dates are different from the current dates.
    Thanks for a terrific product!

    Reply
  • I like the feature but I keep losing the Gantt Chart.
    If I delete a row, my chart is emptied and the dates for the chart go to 01/00/00.
    I was trying to add about 20 rows by highlighting 20 rows and doing insert. Totally removed the color bars in the chart. Choosing Undo removes the inserted rows but does not restore the chart.
    Is there a way to re-map/re-draw the graph if it is wiped out?

    For Vansh Sharma — I found these 2 columns in your B. They are hidden in columns N and O.

    Thanks
    John D

    Reply
    • @John D … It’s hard to know what is going on without having you email the file. When deleting a row, if all the dates change, then some other date was likely linked to the date in the row that was deleted. Inserting rows should not change things in other rows, so something weird is going on in your file. Email for help and attach the spreadsheet after inserting rows if you want additional help.

      Reply
      • User error on my part.

        Reply
  • Jon
    Am using Gantt Chart Pro Beta(purchased).Looking through FAQs and Forum to find how to have Project Level Start and End dates.
    I’ve been thinking of creating a non-WBS row before WBS 1 and using =Max(zx:zn) to fill in an end date.
    Will Gantt color bar be useable to track the end-to-end project timeline, assuming I manually put in a %Complete.
    What pitfalls to watch for?
    Thanks
    John D

    Reply
    • @John. Try it and see. You don’t need a WBS number for the bars to show up. If you copy a template row and insert it above WBS 1, then the bars should show up if you enter the start/end/days. A project end date is probably something I should add below the Project Start Date, and it would be calculated using MAX().

      Reply
      • Jon
        Thanks.
        I tried it and it works nicely.
        I agree with your idea about adding the Project End Date somewhere. Under the Project Start Date is fine.
        John D

        Reply
  • Jon
    Where is the array for Urgency.
    I’d like to use the urgency colors but want to use different number of days.
    Thanks
    John D

    Reply
    • @John, The urgency values are defined in the Help worksheet.

      Reply
  • For the Gantt pro, can you sort by Task Lead?

    Reply
    • @John, If you are using the automated WBS numbering formulas instead of entering WBS numbers manually, then you shouldn’t sort. Possible to sort, yes, but if you do it will likely mess everything up unless you enter WBS numbering manually. You can use Excel’s filtering feature because that just hides rows instead of sorting them.

      Reply
  • I want Work days include the weekend. How can i do it?

    Reply
    • @Henry, In the gantt-chart_o365.xlsx and gantt-chart_intl.xlsx versions, you can define exactly what is considered a “work week” via the options in the Help worksheet. For the version shown in this blog article (file gantt-chart.xlsx) you can use the template rows that calculate the end date based on calendar days instead of work days if your work week is a 7-day period. The demo video and help worksheet explain how to use the different template rows to define the start/end dates and durations in different ways.

      Reply
  • Can you make a video of it?
    I don’t quite understand how to use it.
    I have bought it, but don’t know how to make it work for my gantt chart.

    Reply
  • I would like to use the “GanttChart-BETA” sheet for one of my projects. I was wondering if there is any way to remove the link box that brings you to this page found at the top of the sheet in Excel. I do not want this box to appear on my printed schedules. Please advise.

    Reply
    • @John … Right-click on the shape object and press Delete.

      Reply
      • Thanks Jon. I should of known to try that.

        Reply
  • HELP!!!! I cannot figure out how to do the predecessors and I’ve spent three days on a huge project schedule that was due today. Can someone talk me through this please… like now? I’m desperate. I am the PMO of the entire project and I cant seem to conquer this simple spreadsheet. 713.202.2757

    Reply
    • @Teresa, You can email me, but I don’t provide phone support. My first suggestion would be to watch the video that explains how to use the template rows. You need to be using the template rows that have the predecessor column highlighted green and either the calendar days or work days highlighted green. These template rows use a formula for calculating the Start Date based on the WBS value in the Predecessor column. If you aren’t using the correct template row, you could also just copy Start Date cell from a template row that uses the Predecessor column and paste it into the Start Date cell of the tasks where you want to use it. To use the Predecessor column, just enter a reference formula like =A25 where A25 is the cell containing the WBS of the predecessor task in row 25. When you email, please either send a copy of the file you are working on, or be very specific about what you are wanting to know or what confusion you are having.

      Reply

Leave a Comment