Tech Tip: How to show calendar day duration in MS Project

This has very little to do with knowledge management or my other favorite blog topics.  But it does have a lot to do with the work I have been doing lately in project management and Critical Chain Project Management.

How in the world do you get MS Project to show you the calendar-day duration of a task when the "working calendar" of the project is a 5-day work week (or a two-shift, 5-day week; or a three-shift, 7-day week)?  It can't be that I have to calculate in my head from the projected start and end dates of a given task.

Amazingly, of the zillions of data elements in MS Project, there is no data column that gives you this information directly.  I asked around some colleagues and someone finally pointed that you can do calculations in some of the columns by using a Customized Field and adding a formula.  Unfortunately, you can't do a simple Excel-like calculation.  It's a function - but the basic idea is that you need to know the number of calendar days between the start and end of the task.  Simple, no?

Here are the steps to do this in MS Project 2007.

  1. In the project Gantt Chart view, right click on the column headers and select the Insert Column option. 
    Caldays2
  2. In the dialog that appears, select Field Name "Number1," set the title to "Calendar Days," and pick your desired alignment, and click OK. This will insert a column with this name. 
    Caldays3
  3. Now we need to add the formula.  Right click on the header area again, and this time select Customize Fields.  A dialog box will appear for customization.  Select the radio button next to Formula under "Custom attributes."  (And then respond OK to the warning that the values will be reset in the column.)  Then click on the Formula button.
  4. Add the formula below to the text area and click OK to close both dialogs.
    int(DateDiff("n",[Start],[Finish])/(24*60)+0.9)
    Caldays5
  5. Now tweak the display of the column to remind you that this is a field that you can't type into.  Once again, right click on the column header and pick Font.  I chose to display the text in italics and Gray color.  The result should look something like this.
    Caldays7

Note: While CCPM generally eschews a focus on dates and hard durations and dates that this Tech Tip suggests, there are some types of activities which must fall along the lines of hard-and-fast durations.  A big example is interactions with government bodies who tell you exactly the durations of their review times.

24 Comment(s)

It may be that in some cases you can simplify things by using elapsed days i.e. enter a duration of 5 edays, to represent 5 calendar days, regardless of the project calendar.

@keyconsulting

Yes, of course. Elapsed time (edays, eweeks, etc) is another way to enter the data. I happen to be working wtih an overlay application that will not use eday information, so the calculation for calendar days is needed.

Denis said:

Great post! Very useful!
Could you please explain last part of formula (24*60)+0.9?

Regards,
Denis
www.amiproject.com - on-line Project viewer

Sure. The DateDiff function in MSProject returns the difference in minutes. Because the Start and Finish values are kept down to the minute (like Start at 15:00 on 15 Nov 2009). So to find the number of Days between Start and Finish, you need to divide by the number of minutes in a day. And round that value with the Int function.

But, since the finish might be at 08:00 the next day, the calculation would return an integer of

Denis said:

Jack,
Thanks for explanation. But why do not use DateDiff("d",[Start],[Finish]) which returns days and does not require to do further calculations?

The "d" option doesn't work if a "one day" task starts at 8 am and finishes at 5 pm the same day. With "d", it returns zero, which ends up not adding up across a group of tasks.

Thank you - this is exactly what's needed for government projects given in calendar days, as you said. I'm finding that the outdented tasks do not count up the calendar days, only the indented tasks. Also do you know how to add a column to the duration and calendar day columns, which would show the number of weeks? I've done one or the other, but not both. Thank you.

To show number of weeks, just use the same formula but divide by seven. I used this formula in the Number2 column int(DateDiff("n",[Start],[Finish])/(24*60)+0.9)/7 and it gives me what look to be the correct values.

Note that both the Calendar Days and this new column take MS Project's assumption about durations. Most 8-hour-a-day tasks will start at 8 am and finish at 5 pm (assuming a one-hour lunch break -- the default calendar settings). This means that if you use pure datediff values, the result will be fractional days and fractional weeks. That's the reason for the extra calculations within my equation here - it gives me full days for the Calendar days calculation.

Awaludin said:

What i should do if i want to show calendar days in a task with many subtasks inside?
the formula that you've given above doesn't working...

That's odd. I would have expected this formula to work with any task line that has a Start and Finish date associated with it. Does the formula work for non-summary tasks, or is it broken altogether? If it works for non-summary, then the issue is with the data for the summary tasks, and you will need to do some trial and error to find the correct field names for [Start] and [Finish]. If it is not working in general, you will need to do some trial and error on the correct format for a formula in your version of Project.

Sorry I can't be of more specific help - I don't have a version of project running locally at the moment.

Bill, PMP said:

Jack, Thank you, Thank you, Thank you!
This very awesomely easy and helpful.

George said:

For the summary tasks: when customizing the field you are given the option to "use formula" for the task and group summary rows. By choosing this you have the formula working for summary tasks as well.

ethan mobley said:

How do I use the formula to get the calendar days from the the start of the project? Basically I need a formula that counts the number of days from Government Notice to Proceed.

The basic form of the formula should be the same. You just need to find the field names for "Government Notice" and "Proceed."

For example, if the start date of the project is set to the Government Notice date and you want to find the difference between then and the start date of the task, the formula would look like this: "[Start]-[Project Start]" - "Project Start" is the value in the Project Information dialog. When editing a Formula, there is a drop down for "Field" that gives you a lot of options to pick from.

If you are trying to find a value from a different row in the project, then I don't know how to do that calculation.

Mike Jack said:

This is a very useful tip. Thanks Jack

NeilG Author Profile Page said:

Good Morning Jack

Wishfull Thinking. Can This Formula Be Enhanced To Exclude Week-Ends Or Only Include Work Days?

Thanks

Neil

The way to exclude weekends is to use the standard remaining days calculation (assuming your standard calendar is a five-day calendar). This calculation was explicitly for the situation where the normal calendar is 5-day, but they needed elapsed calendar days.

D Andrew said:

If you want it to appear the same as the duration column then use a Text column and adjust the formula to:

Str(int(DateDiff("n",[Start],[Finish])/(24*60)+0.9)) & " days"

Jerry Queen said:

What if you wanted to do as Ethan Mobley asked - calendar time since the start of the project (in weeks), but you wanted more precision on the result. I'd like to show "2.3 wks" instead of the integer "2 wks" which the formula would return?

You would simply need to change the formula to report some decimal places. I have the "INT" in there to chop off all the decimals, but if you really want them, you can change the formula to

DateDiff("n",[Start],[Finish])/(24*60)
. Note that the "+0.9" in the original formula is a "hack" to get a round-up effect.

And then you would need to rely on the column formatting to display the appropriate number of decimal places.

Ram Author Profile Page said:

by Andrew above on Oct 24 2012
Str(int(DateDiff("n",[Start],[Finish])/(24*60)+0.9)) & " days"

gives an error. Not sure what is missing. Thanks.

Ram Author Profile Page said:

Figure it out. Need to use Text field. Thanks

Abbas Khan Author Profile Page said:

The another way to calculate duration as per calendar:

DateDiff("d",[Start],[Finish])+1

DateDiff calculates the difference of two dates (i.e. start and finish) and "d" indicates days, means value is return in days
you can calculate the datediff in other date formats, eg. "n" is for minutes, "h" is for hours....etc.

Because Datediff gives 1 value less of total duration, so we have to add 1 in the formula.

thanks! I think there was some issue with the "d" parameter and MS Project doing funny things with nonworking days. But then, maybe I just got it wrong way back then.

Leave a comment


Previous entry: Too many good ideas, not enough resources

Next entry: Mathemagenic as a thesis, still familiar

Picture a steaming coffee cup. Better yet, grab one and have a read!

KJolt Memberships

Follow jackvinson on Twitter

View Jack Vinson's profile on LinkedIn