Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    DateDif "md" returning excessive numbers

    Excel 2007, SP2
    Date1 is 25/5/2011
    Date2 is 11/1/2012

    DateDif returns:
    0 years when using "y"
    7 months when using "ym"
    181 days when using "md"


    I've searched on this error and get conflicting reasons of "it's a leap year" or "it's because february doesn't have 31 days"
    ...but so far, no quick fix solutions.

    I've tried to reconcile the 181 to each of the dates but none of the results resemble any of my given dates.

    as anyone seen or experienced this before, and if so, is there a solution?
    Thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    DateDif in 2007 is buggy (those dates return 130 using "md" for me). You may want to have a read of the lengthy discussion here. (if there's a better date formula guy than Barry, I have yet to come across him/her)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    simmo7 (2012-01-16)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thanks Rory. I've implemented the calc for counting each indiivdual element of y/m/d rather than relying on datedif.
    I did notice however that the error crept in when the start day date was of a greater figure than the current day date...i.e. if the original date was 10/01/2011 then the result for todays date was 1 year, 0 month and 1 day
    but if the original date was 12/01/2011 then the result was 0 year, 11 month and 194 days

    Cheers
    Alan

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, in 2010 it returns 17 (i.e. counts from 25th to 11th of next month)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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