Formula for comparing two finish dates 1

[gogglebox]

Hi All,

I'm new to the forum and don't know if anyone could help.

I need a formula to be able compare the movement of two finish dates. For example;

If task = 100% Complete then 0
If finish date is greater than finish1 date then 1
If finish date is less than finish1 date then 2
If finish date is equal to finish date then 3
If either finish date or finish1 date is 'NA' then leave blank.

I could then add indicators to show the movement.

I hope this makes sense and would be really grateful if anyone knows the formula that could produce this outcome or point me to someone who can.

Many thanks

Gogglebox

 

[johnherman]

What have you tried so far?


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[gogglebox]

I have tried many variants using the following formulas as the basis but can't get the finish date is equal to finish date to work. Any suggestions?

IIf([% Complete]=100,"0",IIf([Finish date]-[Finish1 date]>10,"1",IIf([Finish date ]-[Finish1 date ]>1,"2","3")))

switch([% Complete]=100,"blue",ProjDateDiff([Finish date ],[Finish1 date])/[Minutes Per Day]<0,"red",ProjDateDiff([finish date],[Finish1 date])/[Minutes Per Day]>0,"amber",ProjDateDiff([finish Date],[Finish1 date])/[Minutes Per Day]=0,"green")

Regards

Gogglebox

 

[JulieInMaine]

Not sure where you are getting your formulas, but the names of the fields are not [Finish Date] and [Finish1 date], they are [Finish] and [Finish1]

See if this works in a text field:

IIf(IsDate([Finish])=No,"No Finish",IIf([Finish1]=ProjDateValue("NA"),"No Finish1",IIf([% Complete]=100,0,IIf([Finish]>[Finish1],1,IIf([Finish]<[Finish1],2,IIf([Finish]=[Finish1],3,""))))))

 

[gogglebox]

Sorry Julie I don't know why I included 'date' must have had a bad day in the office. Anyway, your formula works great.

Thank you for your help.

 

[JulieInMaine]

You're welcome and thanks for the feedback.

 

[gogglebox]

Hi Julie,

I have a problem with the results of this formula.

I copy all the task dates from finish to finish 1 and there are exactly the same right down to the minutes. However, the results I get are either 1, 2 or 3 and should be only 3. Any thoughts why? Is there a way this formula only calculates the difference between dates and ignores everything else?

Regards,

Gogglebox

 

[JulieInMaine]

Without seeing the file, I can't explain why the results are not all 3. Does pressing F9 to recalculate change anything?

 

[gogglebox]

Hi Julie,

I have pressed f9 and makes no difference. I know you can't view seconds in project but wondered if project calculates the difference at that level which could be the reason why I'm getting the unexpected results. I'm therefore asking can the code be amended so that it only calculates the difference between the dates only and ignores minutes/seconds.

Many thanks

 

[JulieInMaine]

I'm not seeing that. In Project 2013 with the latest updates installed, copying the finish of an autoscheduled task to Finish1, the dates and times match. The formula returns a 3.

So, what version of Project are you using?

 

[gogglebox]

Hi Julie

I'm using 2010

I would be interested to know;

- Even though it can't be shown, in the background, does project schedule in seconds or are minutes the lowest measurement?
- Can the formula be amended so that it only compares between two dates and ignores minutes/seconds?

Your help and advise is much appreciated

Gogglebox

 

[JulieInMaine]

Project schedules to the minute - so seconds shouldn't be playing a role. If you did a copy/paste then there should be no variation in dates. You can just pull the date value out with a DateValue([Start]) to work with just the date (no time) part of the value. If you want to post the file to a shared account, I can take a look.

 

[gogglebox]

Hi Julie,

The file contains restricted information so I can't send it to you unfortunately.

How would I amend your formula 'IIf(IsDate([Finish])=No,"No Finish",IIf([Finish1]=ProjDateValue("NA"),"No Finish1",IIf([% Complete]=100,0,IIf([Finish]>[Finish1],1,IIf([Finish]<[Finish1],2,IIf([Finish]=[Finish1],3,""))))))' with a DateValue([Start]) so that only dates values are compared and ignores minutes?

Cheers

Gogglebox

 

[JulieInMaine]

Wrap the comparisons of [Finish] < [Finish1]. So for example IIf(DateValue([Finish])> DateValue([Finish1]

 

[gogglebox]

Thanks Julie for all your help.

 

[JulieInMaine]

You're welcome and thanks for the feedback.