DATEDIF

Introduction

The DATEDIF() function is almost a secret as it is essentially undocumented by Microsoft although you will find a help page on the MS web site. However, the function is very helpful for anyone who needs to find the age of something or to find how long something has been going on.

Syntax

The syntax of DATEDIF() is

=DATEDIF(Date1,Date2,Interval)

Where

  • Date1 is the from date … the oldest date
  • Date2 is the to date … the latest date
  • Interval tells Excel whether you want the answer in
    • Y = years
    • M = months
    • D = days
    • YM = remainder months having found years and days
    • YD = remainder days having found the years
    • There is another interval option MD but even MS says don’t use it!

Example

Let’s find the age of my dog that was born on 16th February 2015

If I enter just =DATEDIF(16/2/2015,22/2/2018,”Y”) I will get 3 years … but that’s not the final answer, which I want to be in the format: Y … M … D.

To find the complete answer I need to create three formulas, with the birth date in B14

  • =DATEDIF(B14,TODAY(),”Y”) = 3 years
  • =DATEDIF(B14,TODAY(),”M”) = 0 months
  • =DATEDIF(B14,TODAY(),”D”) = 7 days

So my dog is 3 years no months and 7 days old, on 22nd February 2018

Download my Excel file to see how I created a much simpler version of these answers:

You are now a DATEDIF() expert but try your hand at these questions, too, before you download and explore my file:

Question 2: How many days have I lived if I was born on 12th November 1952?

Question 3: Your Asset Register shows the following three assets as having been acquired at the dates given: find their ages in months

Car 22nd February 2014
Cardboard forming machine 1st January 2001
Canteen furniture 19th September 1999

The answers are fully worked in my file!

That’s it! A genuine Excel secret unveiled! It could save you hours of frustration if nothing else!

Duncan Williamson

22nd February 2018

Excel file here datedif_function