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.
- In the project Gantt Chart view, right click on the column headers and select the Insert Column option.
- 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.
- 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.
- Add the formula below to the text area and click OK to close both dialogs.
int(DateDiff("n",[Start],[Finish])/(24*60)+0.9)
- 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.
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.
12 Comment(s)
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.
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
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.
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.
Jack, Thank you, Thank you, Thank you!
This very awesomely easy and helpful.
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.
Leave a comment
Previous entry: Too many good ideas, not enough resources
Next entry: Mathemagenic as a thesis, still familiar
Syndication Options
KJolt Memberships
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