Results 1 to 5 of 5

Thread: Recommendation: Do not use the undocumented DATEDIF function

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    Recommendation: Do not use the undocumented DATEDIF function

    I know others disagree with me on this, but I would recommend not using DATEDIF, especially if the worksheet will be used for something important. Here is a post I have given in the past explaining why I am making this recommendation...

    You might want to reconsider using the DATEDIF function. It is an undocumented (and, thus, probably an unsupported) Excel function which appears to be broken in XL2007 at Service Pack 2. Someone recently posted this message as part of a newsgroup question...

    ************************************************** *******************
    =DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

    In XL2003, the above formula gives me the correct answer of 9. However,
    in Excel 2007, it gives me 122. The 122 increases in value until it
    hits 143 on 1/26/2012 and then, on 1/27/2012, the difference becomes 0.
    ************************************************** *******************

    An informal survey of fellow MVPs shows the above formula works correctly in the initial release of XL2007 and its SP1, but does not work correctly in SP2; hence, it appears to be broken at that level. The problem is that the extent of the breakage is unknown (and probably indeterminable). In addition, I would say, being an undocumented (and, thus, probably an unsupported) function, the odds of Microsoft spending the time to search down and fix whatever broke is slim. In addition, again because it is probably unsupported, the extent of any future breakage in the function due to other code change Microsoft makes elsewhere in Excel is unknowable... something that works today may not work tomorrow and Microsoft will probably never fix it. This would seem to mean that DATEDIF cannot be counted on to work correctly from XL2007 SP2 onward. And even if Microsoft did fix the problem in a subsequent Service Pack, any of your users who remained at SP2 would be subjected to incorrect result.

    ADDITIONAL FOLLOW-UP #1
    -------------------------------
    There is an indication that this might have been fixed in XL2010, however it remains broken in XL2007 as SP2, so if you are in a mixed environment of these two versions, you would be asking for trouble to continue using it. And even if you move wholly to XL2010, there is always the "they broke it once so what would stop them from breaking it again" possibility. After all, I doubt that Microsoft deliberately set out to break DATEDIF in the first place, so the breakage was probably due to an unanticipated side effect from some other change Microsoft made to Excel in SP2. Given that, whose to say it won't happen again? Personally, I am still recommending that DATEDIF not be used, but the final decision as to how much you are willing to risk your data to this undocumented function is up to you.

    ADDITIONAL FOLLOW-UP #2
    -------------------------------
    A Microsoft Answers forum regular named joeu2004...

    joeu2004 - Microsoft Answers

    posted this link...

    DATEDIF function - Support - Office.com

    which documents DATEDIF for the "Microsoft Office SharePoint Server 2007" and "Windows SharePoint Services 3.0". Since the first one is an extension of Office, one might conclude that DATEDIF is "officially" documented. Personally, I don't conclude that, but to be fair I thought I would include the link and let you draw your own conclusion. Personally, I am still holding firm to my recommendation that DATEDIF not be used anymore (for the reasons I gave in the paragraph before my ADDITIONAL FOLLOW-UP #1).
    Last edited by Rick Rothstein; 10-06-2020 at 09:25 AM. Reason: Added link back to another one of my articles.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Great insight. Never got in to such details. Thanks for sharing this Rick
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    I think "md" part can replace by EDATE & DATEDIF("m") part (Fully not sure)

    =DATE(2012,1,5)-EDATE(DATE(2009,6,27),DATEDIF(DATE(2009,6,27),DATE (2012,1,5),"m"))

    Here is the details.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-27-2024 at 02:08 PM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Another thread where it discuss.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    May 2015
    Posts
    1
    Rep Power
    0
    Rick,

    Row\Col
    A
    B
    1
    12/31/2014
    2
    01/01/2015
    3
    4
    1
    A4: =xlDATEDIF(A1, A2, "y")
    5
    0
    A5: =DATEDIF(A1, A2, "y")
    6
    7
    1
    A7: =xlDATEDIF(A1, A2, "m")
    8
    0
    A8: =DATEDIF(A1, A2, "m")


    Is this behavior by design?

    EDIT: I see you already recognized this in the other thread.
    Last edited by shg; 05-17-2015 at 12:09 AM.

Similar Threads

  1. 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
  2. Excel Datedif - Days of February
    By Excelfun in forum Excel Help
    Replies: 6
    Last Post: 06-10-2012, 02:32 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
  •