Calculate difference between dates

[Trevor Rabey]

I have Deadlines set on some Tasks and I am trying to calculate the
difference in Calendar/Elapsed Days (ie not from the project calendar or any
of the others) between the Finish (Actual or Planned) and the Deadline.
More specifically, I only want to see a result, preferably a negative number
only, for those Tasks which have Deadlines and which are Finishing Later
than the Deadline.
First, I would settle for positive and negative results, but I don't want to
see #ERROR in the calulated field if there is no Deadline set for that Task.
Have tried using both a spare Duration Field and a spare Text field.
Have tried IIf Deadline = NA (or "NA" or "") then "", else
DateDiff("d",Deadline, Finish)
Have also tried getting a Flag field to read the Deadline field and go
Yes/No (to help the calculation in the other field) depending on whether a
Deadline is set, ie Yes if Deadline <> "" or Deadline <>"NA" or Deadline >
some Date.
Everything I use to try to return a blank for no Deadline set, instead
returns #ERROR
It never gets as far as the test.
Even if I test for IIF(Datediff(etc) = #ERROR, etc) I still get #ERROR

I'm tired. It's late. I give up.

 

[Jan De Messemaeker]

Hi Trevor,

Try Deadline>2^30
HTH

 

[Trevor Rabey]

Well, I never woulda guessed that.

Hi Trevor,

Try Deadline>2^30
HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620

 

[Steve House [Project MVP]]

LOL - yep, a date that's displayed as "NA" is actually a very large integer
number! Go figure!

 

[David M C]

It's because a date field is actually (in MS Access speak) a Date/Time field.
In Access, a Date/Time field cannot have text stored in it, so to represent
an "NA" you use a very large date (which is really just an integer counting
days(?) from some date). Project is programmed to display this large integer
as NA.

The MS Project database reference is pretty useful, even if you never intend
to access it using Access.

Dave

 

[Steve House [Project MVP]]

Oh yes, I'm well aware of that - was just commenting that it comes as a
surprise to a lot of users who don't realize Project has a database under
its hood. One of the differences between the Project db and an Access db is
Access uses a floating-point Julian number (I tell my classes it's the same
things as a Stardate except Access uses 1/1/1900 as day zero while the
Enterprise uses the date of first contact with the Vulcans LOL) to represent
date/time while Project uses an integer count of 0.1 minute "ticks."

--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs