none
How to compare Dates without consideration to the start time in Project VBA? RRS feed

  • Question

  • I am trying to compare 2 dates of tasks in Project 2012  and receive an error since the start time is different

    1/10/2014 3:00:00 PM  1/10/2014 8:00:00 AM 

    I set the default start time as 8:00 AM in the file -> options- Schedule but it doesn’t impact those task from some reason.

    how to I annulet the time from the date ? or how to i make sure that I have control over the time in the task?

    This is the code :

         SubProjTaskStartDate = Application.ActiveProject.Tasks.UniqueID(19).Start

         If ActiveProject.Tasks.UniqueID(9).Start = SubProjTaskStartDate Then

           MsgBox "Start dates are Equal"

      Else

            MsgBox "Error in start date In Unique ID - " & ActiveProject.Tasks.UniqueID(9)

           End

         End If

    thanks 

    Vered 

    Thursday, March 28, 2013 1:33 PM

Answers

  • Int will remove afternoons just as well as mornings - I think you are confusing it with Round, which would do that.

    But I prefer Int to Fix because I like its behavior in the range of -1 to1 better (Just my mathematical preference for well behaved functions) and because Int is available as a built-in Excel worksheet function where Fix is not. I never have to deal with negative numbers as dates since Excel does not allow it, and I rarely use INT outside of argument used to create values for comparison.



    Friday, March 29, 2013 3:32 PM

All replies

  • I don't have Project to test this on, but dates in VBA are numbers whose integer part is the date, and decimal part is the fraction of 1 day to represent the time. If you only want to compare the dates, you simply need to remove the time part by using Int (assuming those values are actual dates and not strings, which is likely):

    SubProjTaskStartDate = Application.ActiveProject.Tasks.UniqueID(19).Start
    If Int(ActiveProject.Tasks.UniqueID(9).Start) = Int(SubProjTaskStartDate) Then
        MsgBox "Start dates are Equal"
    Else
        MsgBox "Error in start date In Unique ID - " & ActiveProject.Tasks.UniqueID(9)
        End
    End If

    Thursday, March 28, 2013 2:57 PM
  • Your code looks to be comparing the same date(.UniqueID(19).Start), but SubProjTaskStartDate  is declared as a long or integer? If you declare it as a Date the compare will work. Otherwise Bernie's method for removing the time from the date works.

    There is a forum for Project development (for Project Server and for VBA, all versions) at:

    http://social.msdn.microsoft.com/Forums/en-US/project2010custprog/threads?filter=unanswered

    If you need to look for answers to other questions, searching there is a good start.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management


    • Edited by Rod Gill Friday, March 29, 2013 3:42 AM
    Thursday, March 28, 2013 7:53 PM
  • Hi Bernie

    Just in case you ever have dates that are in the very-past, better use Fix() instead of In()
    Her an example for the immediate window:

    ? cdate(int(cdate("29.12.1899 16:00:00")))
    28.12.1899

    ? cdate(fix(cdate("29.12.1899 16:00:00")))
    29.12.1899

    As you can see: working with Fix() also workes for dates before dec 30, 1899, Int() doesn't.

    alternatively you can use
    yourDate = Now()
    ? dateserial(year(yourDate), month(yourDate), day(yourDate))

    I for myself prefer to converting it to a compareable date format like
    ? Format(now(), "yyyymmdd")
    20130329

    Henry

    "Bernie Deitrick"; "Excel MVP 2000-2010" schrieb im Newsbeitrag news:b816c32e-f64a-4856-9096-183c5ba018e5@communitybridge.codeplex.com...

    I don't have Project to test this on, but dates in VBA are numbers
    whose integer part is the date, and decimal part is the fraction of 1
    day to represent the time. If you only want to compare the dates, you
    simply need to remove the time part by using Int (assuming those values
    are actual dates and not strings, which is likely):

    SubProjTaskStartDate =
    Application.ActiveProject.Tasks.UniqueID(19).Start
    If Int(ActiveProject.Tasks.UniqueID(9).Start) =
    Int(SubProjTaskStartDate) Then
       MsgBox "Start dates are Equal"
    Else
       MsgBox "Error in start date In Unique ID - " &
    ActiveProject.Tasks.UniqueID(9)
       End
    End If

    Friday, March 29, 2013 8:04 AM
  • Thanks, Henry.

    That's not an issue that I've ever run into, since Excel does not allow dates before 1900.

    > As you can see: working with Fix() also workes for dates before dec 30, 1899, Int() doesn't.

    But Int() is fine for comparing dates since both would be off by one day in the same direction.


    Friday, March 29, 2013 2:12 PM
  • That's wrong! Morning and afternoon for these past days will result in different days using Int(). That's why I mentioned it. Why not just always use the one that works? You have to write the same amount of characters. Or what is the reason to not to use Fix()?

    Henry

    Friday, March 29, 2013 3:01 PM
  • Int will remove afternoons just as well as mornings - I think you are confusing it with Round, which would do that.

    But I prefer Int to Fix because I like its behavior in the range of -1 to1 better (Just my mathematical preference for well behaved functions) and because Int is available as a built-in Excel worksheet function where Fix is not. I never have to deal with negative numbers as dates since Excel does not allow it, and I rarely use INT outside of argument used to create values for comparison.



    Friday, March 29, 2013 3:32 PM
  • Hi,<o:p></o:p>

    Thank you all for the useful help.<o:p></o:p>

    I have used the "Int" option that Bernie suggested and it worked !<o:p></o:p>

    have a great week.<o:p></o:p>

    Vered <o:p></o:p>

    Sunday, March 31, 2013 6:54 AM
  • Hi Benrie

    Sorry for the confusion. You are of course right, it's not rounded but truncated to the next whole number. Comparison between dates also works on these old dates. As I come from the database side where you have userinputs (dates only) to have to be compared to dates in the database or input to be truncated to dates only without time and stored in the database this gave troubles in the past using INT() instead of FIX() because INT made a different date out of the entered dates before 1900 than what the user entered.
    In your case, using excel dates only that can't be "negative" dates it doesn't matter at all and will give the same result.

    BTW: we are talking about VBA, not Excel built in functions here (as mentioned in the subject). Fix is implemented in VBA.

    Henry

    "Bernie Deitrick"; "Excel MVP 2000-2010" schrieb im Newsbeitrag news:d74f92ee-38dc-4cfd-933c-8f1218ea4045@communitybridge.codeplex.com...

    Int will remove afternoons just as well as mornings - I think you are
    confusing it with Round, which would do that.

    Monday, April 1, 2013 6:10 AM
  • > BTW: we are talking about VBA, not Excel built in functions here (as mentioned in the subject). Fix is implemented in VBA.

    Yes, I know that, but I was simply explaining my preference for using INT instead of FIX - the commonality between INT in the user interface (the worksheet) and VBA means that the code is a little more understandable to others since they are more likely to be familiar with INT.

    Bernie

    Monday, April 1, 2013 1:48 PM