Excel Datedif - Days of February

**Excelfun** · 06-10-2012, 11:31 AM

Hi Rick
Yes i agree with you; but i want result as 0 Years 1 Months 0 Days
PFA workbook hope this will help.
I have Date1 -01-APR-10 And Date2-31-MAR-11
I enter no of days leave suppose 31 days then; I deduct 31 days from Date2
and i get Date3- 28-02-11 in another cell. Then i use datedif for Date3 And Date2.
it reflect for 31 days - 0 Years 1 Months 3 Days. it should be 0 Years 1 Months 0 Days

And does this problem exist in only 2007 ?

**Rick Rothstein** · 06-10-2012, 02:08 PM

Originally Posted by Excelfun

Hi Rick
Yes i agree with you; but i want result as 0 Years 1 Months 0 Days
PFA workbook hope this will help.
I have Date1 -01-APR-10 And Date2-31-MAR-11
I enter no of days leave suppose 31 days then; I deduct 31 days from Date2
and i get Date3- 28-02-11 in another cell. Then i use datedif for Date3 And Date2.
it reflect for 31 days - 0 Years 1 Months 3 Days. it should be 0 Years 1 Months 0 Days

And does this problem exist in only 2007 ?

No, this "problem" will exist in all versions of Excel. The problem is what I said in my last message...

"I think reporting a date difference using months as one
of the units of time is a terrible idea. Why? Because the
number of days involved depends on the start date"

Let me ask you some questions and see if this will highlight the problem as I see it. First, to restate your expectation... you think February 28th (non-leap year) to March 31st should be "1 Month 0 Days", right (end of month to end of month I presume)? Okay, what should the difference be between February 28th (non-leap year) and March 28th or March 29th or March 30th? Before you answer, think about what the difference should be between February 14th (non-leap year) and March 14th? How about March 15th or March 16th and February 14th? One final difference for your to consider before you answer... what is the differences between the start dates January 28th, 29th, 30th and 31st and the ending date of February 28 (non-leap year)?