Friday, February 8, 2019

MS Project formula: ProjDateDiff versus DateDiff

It is always fun to play with formulas in MS Project. It reminds me when I started in 2006 working with MS Project doing scheduling support for project managers. Recently I add a new request about formulas from a customer.
He needed to enter a custom deadline for tasks or milestones. He could use the default deadline column since it was already used for another purpose. Thus we created a custom deadline new field. Then we wanted to have the delay between the finish date and the custom deadline, with a sum rollup at project level to obtain the overall delay.

So far not a big deal... To calculate the delay, I started as usual by using the ProjDateDiff function. Note that I divided by 480 (480 minutes in a 8hrs-day) because this function returns a number of minutes.

However when I give a try, here is what I got if I set the custom deadline equal to the finish date.

Here is the problem, I got 1 day of delay while both date are the same. The trick is to display the time in the date format. 

My task is a 1-d task thus the finish time is at the end of the day (6pm). When I set the custom deadline, by default it will be at the start of the working day (9am). Thus the ProjDateDiff returns a 1-d delay. 

So I search for a couple of minutes around the date functions in MS Project and found the DateDiff function (DateDiff(interval, date1, date2)). It has an additional property that is the time unit or interval. Thus you can set that you need to consider days only in the date difference.

Now I get the difference between the two dates, only considering the date and not the time.

Share this article :


  1. DateDiff("d",NOW(),[Finish])
    ¿Is there any way to cosidere only days between Monday to Friday? I wouldn't like to considere Saturday and Sunday. Thanks

    1. Hi, the ProjDateDiff formula only considers working days, and exclude weekends and non-worked days.