Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    DATEDIFF()?? (XP)

    Is there logic that I am not seeing in the following results using DateDiff("m",[tmpTest]![startDate],[tmpTest]![endDate]) :
    startDate endDate intMonths
    1/1/2000 1/30/2000 0
    1/1/2000 2/1/2000 1
    1/1/2000 2/29/2000 1
    1/1/2000 12/31/2000 11
    1/31/2000 12/31/2000 11


    Shouldn't Jan 1-Dec 31 be 12 months?
    Shouldn't Jan 1 - Feb 29 be 2 months?
    Shouldn't Jan31 - Feb 1 be 0 months?

    Perhaps I am using the wrong function or the right function in the wrong way. What I am attempting to accomplish is calculate the number of months between two dates, and based on the above I am getting information of little use (aka "useless information"). There is a function in Excel, "YEARFRAC()", that does exactly what I am seeking, and I thought the DateDiff was essentially the same.

    Any ideas and thoughts are greatly appreciated.

    Ken

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: DATEDIFF()?? (XP)

    Oops! I found the old "standby" worked for this as well. "((([EndDate]-[startDate])/365.25)*12)" where resulting field has zero decimal places

    I would like to know more about the DateDiff function, if anyone has time and inclination.

    It defies logic in my small brain. What WOULD be an effective use of the DateDiff?

    Thanks again.

    Ken

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DATEDIFF()?? (XP)

    DateDiff is an extremely simple (stupid, if you like) function. DateDiff("m", Date1, Date2) subtracts the month of Date1 from the month of Date2, regardless of the day of the month of both dates. If you need that, fine. Otherwise, you'll have to use more complicated functions (perhaps utilizing the result of DateDiff)

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: DATEDIFF()?? (XP)

    Thanks Hans. It is me that is dumb, and not the function. Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •