Excel Datedif - Days of February

[Excelfun]

Hi

if i take in column A (Date1) 31-MAR-11 ; in column B No of days are 31; in column No C there is formula which deduct no of days from column A date.
so answer is 28-FEB-11 (Date2); in column D the formula is =DATEDIF(C2,A2,"ym") & " Months "&DATEDIF(C2,A2,"md") & " Days"
which shows 1 Months 3 Days

how can i rectify this error. i think excel consider 31 days for the month of Feb

i will appreciate any help on this. for rest of date range it works fine for me.

[Rick Rothstein]

I would recommend you read this article which I have posted elsewhere in this forum..

Recommendation: Do not use the undocumented DATEDIF function (http://www.excelfox.com/forum/f22/recommendation-do-not-use-undocumented-datedif-function-321/)

and then consider using the code I have posted in this other article, also elsewhere in this forum...

UDF (user defined function) replacement for Excel's DATEDIF function (http://www.excelfox.com/forum/f22/udf-user-defined-function-replacement-excels-datedif-function-322/)

[Excelfun]

Hi Rick

Thanks for reply and sharing this info. but still i am getting error with UDF
my formula =xlDATEDIF(C1,A1,"YM") & " Months "&xlDATEDIF(C1,A1,"MD") & " Days"
results 1 Months 3 Days


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

First off, 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... a well-fixed unit of measure should not vary at all if it is to have any meaning. With that said, why do you think 1 Month 3 Days is wrong? From February 28th to March 28th is one month (you use the start date's month to count from), then March 29th, 30th and 31st is another 3 days. What were you expecting the answer to be?

[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 ?

[Rick Rothstein]

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)?

[Excelfun]

thank u so much Rick for your time and Help..!
i will check and get back on this.