Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Date Difference Formula RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 ts As Tasks
        Dim t As Task
        Dim Startx, Finishx As Date
        Dim dur As Integer
        Set ts = ActiveProject.Tasks
        
        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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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