DateDif “md” returning excessive numbers

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

FWIW, in 2010 it returns 17 (i.e. counts from 25th to 11th of next month)

Reply | Quote