Results 1 to 7 of 7

Thread: Excel Datedif - Days of February

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0

    Excel Datedif - Days of February

    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.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I would recommend you read this article which I have posted elsewhere in this forum..

    Recommendation: Do not use the undocumented DATEDIF function

    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

  3. #3
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0
    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
    Last edited by DocAElstein; 06-12-2023 at 01:03 PM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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?

  5. #5
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0
    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 ?
    Attached Files Attached Files

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Excelfun View Post
    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)?

  7. #7
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0
    thank u so much Rick for your time and Help..!
    i will check and get back on this.

Similar Threads

  1. Recommendation: Do not use the undocumented DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 05-16-2015, 10:06 PM
  2. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  3. Replies: 0
    Last Post: 09-17-2012, 02:05 PM
  4. The Number of Years, Months and Days Between Two Dates
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 7
    Last Post: 06-08-2012, 10:35 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:00 PM

Posting Permissions

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