# Date Difference Formula

• ### Question

• Hi,

I got a real strange request from my client and I am not able to figure out a formula for it.

He wants a date difference formula which takes the date diff from the start date of task 1 and the actual start date of a different task. I tried making an additional field which points to the actual start of task 2.  Here is what I have so far

IIf([Task Name] = "LRR", ProjDateDiff([Start], [PSR Actual Start]), 0)

(PSR Actual start is a field which points back to actual start date for the PSR Task) here is the formula for that

IIf([Task Name] = "PSR", [Actual Start], 0)

I suspect that the issue is using 2 different tasks.  Even when I break the formulas down to the simplest element, as soon as I try to to calculate anything in 1 task that reference the 2nd task it breaks.  Anyhow, if someone could help me come up with a formula to calculate a duration using fields from different tasks, Iwould appreciate it.

Thanks,

Thursday, June 9, 2011 10:38 PM

• Thanks to everyone who responded. As Dale and Rod mentioned, custom fields do not support doing calculations for dates on 2 different tasks.  However, I was able to come up with a work around for my situation.  what I did was create task level custom date fields which pointed back to the start (or actual start depending) for the tasks That I wanted to calculate the date difference. I rollup up the formulas as max. For example a field called PSR actual start  IIf([Task Name] = "PSR", [Actual Start], "NA")  Result was the start or actual start date for the tasks at the project summary level.  I then used a project level date custom field which pointed back to the task level custom fields.  example  PSR Date =[PSR actual start]. Once I had the project level fields for the 2 tasks that I wanted to do the date diff, I then created a project level ProjDateDiff field.  ex ProjDateDiff([LRR Start Date],[PSR Date],"Holiday Calendar") The final output is a project level field which displays the duration of the LRR Start Date and the PSR actual start date in days.

Friday, June 10, 2011 5:04 PM

### All replies

• bikerjohn1 --

What your client wants you to do cannot be done.  It is not possible to calculate date differences between two different tasks, as that is NOT how formulas work in Microsoft Project.  Even though it can be done in a tool like Excel, it CANNOT be done in Microsoft Project...period.  Sorry.  Hope this helps.

Dale A. Howard [MVP]
VP of Educational Services
msProjectExperts
http://www.msprojectexperts.com
http://www.projectserverexperts.com
"We write the books on Project Server"

Thursday, June 9, 2011 11:08 PM
• What if I made  a project level field to replace task 1 that is manually entered via project information, would it work then?

Friday, June 10, 2011 12:24 AM
• Bikerjohn1 --

Well, the best I can tell you is to "go for it" and let us know the result, if any.  :)

Dale A. Howard [MVP]
VP of Educational Services
msProjectExperts
http://www.msprojectexperts.com
http://www.projectserverexperts.com
"We write the books on Project Server"

Friday, June 10, 2011 12:58 AM
• Dale is correct The only work arounds are Project VBA code or using PSI code.

Rod Gill

The one and only Project VBA Book Rod Gill Project Management
Friday, June 10, 2011 7:45 AM
• Hi

Just to add on Rod's reply, I had also faced the same issue when I had to calculate the date-difference(excluding weekends and holidays) for two different tasks, so I have used following VB script to achieve the same.

Sub DateDiff()
Dim Startx, Finishx As Date
Dim dur As Integer

Set t = ts(5) 'Replace 5 with task index
Startx = t.Start
Set t = ts(7) 'Replace 7 with task index
Finishx = t.Finish
dur = (Application.DateDifference(Startx, Finishx, Standard) / 480)
MsgBox (CStr(dur) & " " & Days)
End Sub

So thought of sharing with you in case it might help you.

Sachin Vashishth MCTS
Friday, June 10, 2011 10:20 AM
• Thanks to everyone who responded. As Dale and Rod mentioned, custom fields do not support doing calculations for dates on 2 different tasks.  However, I was able to come up with a work around for my situation.  what I did was create task level custom date fields which pointed back to the start (or actual start depending) for the tasks That I wanted to calculate the date difference. I rollup up the formulas as max. For example a field called PSR actual start  IIf([Task Name] = "PSR", [Actual Start], "NA")  Result was the start or actual start date for the tasks at the project summary level.  I then used a project level date custom field which pointed back to the task level custom fields.  example  PSR Date =[PSR actual start]. Once I had the project level fields for the 2 tasks that I wanted to do the date diff, I then created a project level ProjDateDiff field.  ex ProjDateDiff([LRR Start Date],[PSR Date],"Holiday Calendar") The final output is a project level field which displays the duration of the LRR Start Date and the PSR actual start date in days.

Friday, June 10, 2011 5:04 PM