none
Internal date storage formats in MS Project

    Question

  • I'm trying to use custom fields to create red-amber-green flags based on how late something is, e.g. compare a deadline to the project's current status date.

    I cannot seem to get things to work the way I expect and even a test formula that just subtracts two dates that I have hand entered in different columns is returning odd fractional dates, e.g. 2.43 instead of 2 for the difference between 12/3/2012 and 12/5/2012.

    Can someone eitehr explain how Project stores dates or point me to documentation on this similar to what is in the help for Excel on the same topi?.

    While I am at it, the next question is how to handle test for the "NA" value that Project automatically puts in a date field that is not filled.

    IIF([field]="NA","true","false") seems to return errors.

    Friday, December 07, 2012 10:11 PM

All replies

  • Ray,

    I can't tell you exactly why you get 2.43 instead of 2 without additional information. It could be that you have re-defined your day to be something other than the default 8 hours (e.g. 6.5 hours) under File/Options/Schedule group. If you use the default settings and put a simple date subtraction formula (i.e. [Date2]-[Date1]) in a custom text field, the formula will indeed yield the difference in days. If you are using an extra duration field (e.g. Duration1) for your formula, the best way to get a date difference is to use either:

    ProjDateDiff( date1, date2, calendar ) for the difference in working days

    or

    DateDiff( interval, date1, date2, firstdayofweek, firstweekofyear ) for the difference in calendar days

    For a quick "explanation" of how to deal with "NA" in date fields, see Jack Dahlgren's article at, http://masamiki.com/project/customfieldFAQ.htm

    A further bit of information. Project stores all time related data in minutes, actually to the nearest 1/10 of minute, if I recall correctly, so just having the date isn't always enough, the time is also important.

    Hope this helps.

    John

    Saturday, December 08, 2012 2:16 AM
  • Project stores dates the same way Excel does: number of days since 1900. The fractional part is time. .5 means midday, .75 6 pm and so on. It's likely your tasks finish during the day rather than at 17:00, hence the fractional part.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Saturday, December 08, 2012 7:59 AM
    Moderator
  • Thanks for taking time.

    My company's projects tend to focus more on whether things are on time or late than on how munay hours it takes to do things.

    Even though it sounds trivial I am trying to do a set of things where I would assign red-amber-green ratings to various conditions. I have been able to do a few of these based on manually entered percent complete figures but on dates, I am washing out pretty badly.

    There must be one or two very simple things I don't get where the is some twist to the type of thing I can do easily in Excel.

    Here is an example. I have a totally vanilla file where just a few minutes ago I set up a new task and  hand entered 12/12/2012 in the "Finish" field for a task and 12/10/2012 for the "Start" field. the MS Project standard display filed called "duration" is showing 3 which means it is count the 10th through the 12th exclusive of the 10th.

    I then set up a new custom field and chose "duration" as the field type and for the formula I have:

    [Finish]-[Start]

    I would expect, based on a good amount of Excel experience that the answer would be 2. If it gave me 3 I would understand that it is counting inclusive of the start and I could work with that. Instead, I am getting '0 days'

    I feel like if I can get one or two of these basic fomulas working clenaly  will be able to build from there.

    Any ideas for me?

    Monday, December 10, 2012 1:59 AM
  • Finish-Start gives you teh number of days, but it isn't a duration. that formula should work in a Number field. In a Duration custom field you need:

    ProjDateDiff([Start],[Finish],"Standard")


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, December 10, 2012 6:30 AM
    Moderator
  • John,

    Thanks for you help with this. You and someone named Rod McGill both pointed me to ProjDateDiff as the main tool and I seem to be making progress.

    I have not tried DateDiff yet. What does the first parameter called "interval" mean?

    Ray

    Tuesday, December 11, 2012 3:41 AM
  • Thanks Rod! Your input was very helpful.
    Tuesday, December 11, 2012 3:42 AM
  • Ray,

    Just for reference there is no "Mc" in Rod's name. He is Rod Gill.

    The "interval" parameter is the desired time interval. It can be "d" (days), "y" (year), and etc. See the following for a complete list:

    http://msdn.microsoft.com/en-us/library/b5xbyt6f(v=vs.80).aspx

    John

    Wednesday, December 12, 2012 3:59 PM