Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following formula in a cell: =DATEDIF(CURRENTMONTH,AK2,"m") where CURRENT is "5/31/09" When AK2 equals 6/30/09, I get zero. When AK2 = 7/31/09, I get 2. What happened to month 1 and how do I avoid this error? I must have the first answer 1, not zero. This issue can throw some big totals off. TYIA!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DATEDIF counts the number of whole months that have passed from the start date to the end date.
    If the start date is May 13, a whole month will have passed 31 days later (because May has 31 days) on June 13, not yet on June 12.
    Similarly, if the start date is May 31, a whole month will have passed 31 days later, on July 1, not yet on June 30.

    If you don't like this, you'll have to specify precisely how you would want to define the date difference in all possible circumstances.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='Torquemada' post='778207' date='03-Jun-2009 11:40']I have the following formula in a cell: =DATEDIF(CURRENTMONTH,AK2,"m") where CURRENT is "5/31/09" When AK2 equals 6/30/09, I get zero. When AK2 = 7/31/09, I get 2. What happened to month 1 and how do I avoid this error? I must have the first answer 1, not zero.[/quote]
    You might want to look at =ROUND(YEARFRAC((CURRENTMONTH,AK2,1)*12,0) (with which I recently struggled in another thread ).

    Edited: my bad, forgot to include "*12"
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='Torquemada' post='778207' date='03-Jun-2009 11:40']I have the following formula in a cell: =DATEDIF(CURRENTMONTH,AK2,"m") where CURRENT is "5/31/09" When AK2 equals 6/30/09, I get zero. When AK2 = 7/31/09, I get 2. What happened to month 1 and how do I avoid this error? I must have the first answer 1, not zero. This issue can throw some big totals off. TYIA![/quote]
    What about something like =ROUNDDOWN((AK2-CURRENTMONTH)/30,0)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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