« Mano a mano | Main | What are you going to use Project Server 2006 for? »

Telling Time - ProjDateDiff, VB DateDiff and Application.DateDifference

Date subtraction in VB, Project VBA and Project custom field formulas is one of the more common activities. Unfortunately there are a number of slightly different functions available. This article briefly describes the main three.

It all starts with the VB DateDiff function.
The syntax is as follows:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
interval is required and is the time unit you want the result returned in:

  • yyyy = Year
  • q = Quarter
  • m = Month
  • y Day of year
  • d = Day
  • w = Weekday
  • ww = Week
  • h = Hour
  • n = Minute
  • s = Second

  • date1 and date2 are required and are the two dates you are working with.
    firstdayofweek and firstdayofyear are optional and will change the defaults from Sunday and the week which contains January 1 to whatever else you might choose.

    Now this is a pretty powerful and useful function, but when you are calculating schedule dates it becomes a problem. The issue is easily illustrated with the example of a weekend. Suppose you want the working hours for a task between now and two working days from now. DateDiff would work fine if both days are in the same workweek, but once the interval spans a weekend then the calculation is wrong. To resolve this, Project VBA has the DateDifference function. DateDifference is considerably simpler. Here is the syntax:
    Application.DateDifference(StartDate, FinishDate, Calendar)

    StartDate and FinishDate are required. They are the start and finish dates used.

    Calendar is optional. It can be a resource or task base calendar object. The default value is the calendar of the active project.

    The result of this function is the duration in minutes. You can convert the minutes into days by dividing by 480 (for an 8 hour day or more accurately dividing by 60 * HoursPerDay. HoursPerDay is a Project property which reflects the current definition of workdays in Project. You can also divide by the HoursPerWeek and DaysPerMonth functions if you want to use longer timescales.
    Application.DateDifference is what you would use in VBA code.


    In Project custom field formulas the situation is almost exactly the same. However instead of being called DateDifference, they named the function ProjDateDiff. The arguments are the same:
    ProjDateDiff( date1, date2, calendar )
    and the result is also returned in minutes. Converting this into usable time periods IS different though. Custom formulas offer the [Minutes Per Day] and <[Minutes Per Week] constants to do conversion to days and weeks. There is no month conversion available.

    Here are a couple of examples:
    Project VBA DateDifference example:
    Sub projectduration()
    MsgBox CStr(Application.DateDifference(ActiveProject.Start, ActiveProject.Finish, standard))
    End Sub

    Custom Field Formula Example:
    ProjDateDiff([Project Start],[Project Finish],"standard")
    Note that the custom field formula requires quotation marks around the calendar name and the VBA example does not.

    RELATED POSTS
  • Setting Microsoft Project Level Custom fields using VBA
  • Iterating through Microsoft Project Subprojects
  • Date Math - DateAdd, ProjDateAdd in Microsoft Project
  • Microsoft Project Undo Levels and Macros
  • VBA and Visual Basic For ... to ... statements
  • VBA to VSTO Tutorial Part Two - Adding a Command Bar and Buttons
  • Office VBA for Mac After 2008
  • Analyze Microsoft Project Resource Usage Data In Excel
  • Microsoft Project VBA - the Instr function
  • VBA Writing to a text file (MS Project, Excel)

  • Comments (2)

    Dan:

    I am trying to use Last Published or Last Updated in a custom field formula. but have not been able to. they say they are invalid fields.

    I can run them in a view.

    Is there a list of Field Names which can be used in Formulas in Custom Fields
    Thanks
    --Dan

    GeekLad:

    I can't get ProjDateDiff to work and it is driving me insane. I'm using MS Project Standard 2003 and I'm trying to create a custom formula using ProjDateDiff and I keep getting #ERROR and Project offers no help to indicate why. I tried ProjDateDiff([Project Start],[Project Finish],"standard") and it doesn't work. Do you have any idea why it doesn't work? I tried also hard-coding two dates (enclosed in double-quotes as well as # signs) and the formula doesn't work that way either. Is there an option somewhere in Project I need to enable in order to make ProjDateDiff work?
    ----------------------
    I'd try leaving the parameter for the calendar blank or use "Standard". - Jack

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

    About

    The previous article is Mano a mano.

    The next article is What are you going to use Project Server 2006 for?.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34