SharePoint: DateDif Function

“DateDif function” will return the difference between two dates based on the unit supplied. The units can be:

• Y(ear)
• M(onth)
• D(day)
• M(onth)D(day)
• Y(ear)M(onth)
• Y(ear)D(ay)

Please note that the difference will always be the whole number of the unit. For example:

=DateDif("2018-01-10","2019-01-09","Y")


will return 0 even if it’s only one day off.

The first three units are quite straightforward. You get the difference in either full years, months, or days between the two dates. Things can get a lot trickier for the remainder 3. Let’s look at each of them individually.

MD Unit

It will return the difference between the two dates in days. The year and the month are ignored, so for:

=DATEDIF("2008-04-10","2019-01-09","MD")


And

=DATEDIF("2001-01-10","2019-01-09","MD")


You’ll get the same value. 30 days.

Note: Microsoft officially recommends not using this unit since it has some limitations and can return zero or negative numbers.

YM Unit

If you use the YM unit, you’ll get the difference in months between 2 dates. SharePoint ignores the year and the day, so for the formula:

=DateDif("2018-01-10","2019-01-09","YM")

And

=DateDif("2001-01-10","2019-01-09","YM")


You’ll get the same value. 11 months.

YD Unit

The DateDif function with the YD unit will return the difference in days between 2 dates. SharePoint ignores only the year, so the month is taken into consideration.

=DateDif("2018-01-10","2019-01-09","YD")

And

=DateDif("2001-01-10","2019-01-09","YD")


Both will return the same value. 364 days.

Usage

There are multiple examples for all the units, so let’s explore some:

Person’s Age

=DATEDIF("2001-01-10",Today(),"Y")

It will 18 years.

Days until the birthday party

=DATEDIF([birthday],[birthdayparty],"D")

It will return the days you have to buy this person a gift.

Baby’s due

=DATEDIF(conceptionday,TODAY(),"M")

It will return the number of pregnancy months.

Formatted String with time elapsed

The other units can be a little bit tricky, but let’s say that you have two dates, and you want to show a string like “X years, Y months and Z days.” To achieve this, you’ll need to do the following:

=DATEDIF(birthday,TODAY(),"Y") & " years, " & DATEDIF(birthday,TODAY(),"YM")& " months and " & DATEDIF(birthday,TODAY(),"MD")& " days"


We need the ”YM” to ignore the years and days and the “MD” to ignore the years and months to return the correct value.

Limitations

1. For the units “Y,” “M,” and “D,” SharePoint will return an error if the first date occurs after the second. In these cases, you should flip them to get the correct value. A simple “If” statement does the trick.
2. Units “YD,” “YM,” and “MD” don’t require that the first date occurs before and therefore SharePoint won’t return an error.

Recommendations:

1. Like mentioned before, SharePoint recommends not using the “MD” unit, so if you use it, be careful to test to ensure that SharePoint calculates all values correctly.
2. Always use uppercase letters for the units. SharePoint accepts them in lowercase, but it makes the formula less readable.
3. Always have the validation of the dates to avoid errors. Make it a best practice to use the following formula:

=IF(DATE1<DATE2, DATEDIF(DATE1, DATE2,UNIT), DATEDIF(DATE2,DATE1,UNIT))


Sources:

Microsoft’s DateDiff Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Featured Image by Curtis MacNewton on Unsplash