Share via

Why does DATEDIF give different results in Excel and SharePoint?

Remster 0 Reputation points
2024-07-02T11:15:45.3633333+00:00

The following formula gives different results in an Excel worksheet and a SharePoint list:

=DateDif([Date_1],[Date_2],"md")

There are ten cases where Excel gives a higher number than Sharepoint, and two cases where SharePoint gives a higher number than Excel:

Date_1 Date_2 Excel SharePoint
03/04/2021 01/04/2027 29 28
28/06/2018 24/06/2024 27 26
30/11/2017 10/05/2025 10 11
07/07/2008 31/03/2027 24 23
29/11/2011 31/03/2025 2 1
01/11/1999 29/11/2024 28 28
04/06/2018 31/03/2024 27 26
30/08/2007 31/07/2021 1 1
02/11/2020 22/07/2024 20 20
01/12/2014 19/12/2024 18 18
27/01/2016 31/03/2027 4 3
20/05/2005 30/04/2025 10 11
01/04/2018 15/09/2024 14 14
22/06/2004 31/03/2027 9 8
30/11/2007 31/07/2024 1 1
01/12/2016 07/11/2024 6 6
13/08/2013 31/07/2024 18 18
08/04/2015 31/05/2025 23 22
17/02/2004 31/03/2026 14 13
20/08/2018 31/03/2024 11 10

Why exactly is this, and what can I do to bring SharePoint into line with Excel?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,653 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,151 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Haoyan Xue_MSFT 21,826 Reputation points Microsoft Vendor
    2024-07-03T02:54:23.62+00:00

    Hi @Remster ,

    Thank you for posting in this community.

    In SharePoint list calculated column settings, the formula you are using includes an unrecommended Unit md, which will ignore difference in month:

    jefferni_1-1668757363037.png

    Reference:https://support.office.com/en-US/client/results?Shownav=true&lcid=1033&ns=SPOStandard&version=16&omkt=en-US&ver=16&HelpId=WSSEndUser_FormulaSyntaxError

    Using another calculation method:

    1.Create a calculated column named "test date", which is the last day of the last full month, calculated as:

    =DATE(YEAR(Date_2),MONTH(Date_2),0)

    2.Create another calculated column named "CALDAY" , align the number of days calculated by SharePoint with Excel. Calculated as:

    =IF(DAY(Date_2)<DAY(Date_1),((DATEDIF(Date_1,[test date],"MD")+1)+(Date_2-(DATE(YEAR(Date_2),MONTH(Date_2),1)))),DATEDIF(Date_1,Date_2,"MD"))

    Result:

    User's image

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.