“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

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

One thought on “SharePoint: DateDif Function

  1. Hello Manuel, can you elaborate on this?

    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.

    I have a calculated column that needs to show Days Until The Event. It should be a positive number if the event is in the future, and a negative number if the days are in the past. How should I adapt your formula?

    =DATEDIF(Today(),[Event Date],”D”) returns #NUM if the answer is negative, as you point out.

Leave a Reply

Your email address will not be published. Required fields are marked *