Why does DATEDIF give different results in Excel and SharePoint?

Question

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?

Answer 1

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:

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:

---

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.