Subscribe for access to this FREE e-learning mini-course!


Your First Name:


Your Surname:


Your Email Address:



Subscribe
Close


Small logo
Hamburger menu
Menu
bookings:   0333 6000 111
service:       0333 6000 555
    or           
Free Video Tutorials : Excel Formulas
Free Video Tutorials : Excel Formulas
Search

EXCEL FORMULAS: THE DATEDIF FUNCTION

Find out what the DATEDIF function is and how to use it in Microsoft Excel

Background information and resources for Excel courses that we hope you find useful

The undocumented DATEDIF function can be incredibly useful when calculating the difference between dates in Excel. It is a bit of a hidden gem, as it doesn't appear in the list of formulas or if you search for it using the Insert Function dialog box. In fact, even when you are typing in the formula, the small screen tips do not appear, as they do with a number of other functions.

It can be used in many different ways, but perhaps one of its most common uses is to calculate a person's age.

USING THE DATEDIF FUNCTION

Take the scenario below as an example:

Using the DATEDIF Function

Step 1. We know the date of birth of the candidates and the current date is in H1 in the form of =TODAY()

Step 2. So we can insert a little formula in C2 to work out their age. Type in =DATEDIF(B2, $H$1, "Y")

Excel Formulas: The DATEDIF function

The first part of the formula refers to the first date value (i.e. the DOB). The second part refers to the end date (i.e. today's date) and the final part ("Y") represents how you want to display the interval between the two dates.

In this example, "Y" is used, so the answer will display the number of complete years between the two dates. It's great to calculate ages because it will not round the figure up.

You can use this formula to calculate the difference between dates in years, days and months. You can also work out the difference between months, ignoring the year and day, and the difference between days, ignoring the month and the year or just ignoring the year.

So the difference in days between 1st Jan 2014 and the 5th Feb 2015 using the "YD" interval, is 35.

DATE INTERVALS AND EXPLANATIONS

Y: The number of complete years.
M: The number of complete months.
D: The number of days.
MD: The difference between the days (months and years are ignored).
YM: The difference between the months (days and years are ignored).
YD: The difference between the days (years are ignored).

WE HOPE YOU FOUND THIS USEFUL

Want to see how we can really help you?

LINKS TO MORE ...

Main Excel Resources Page

Excel Resources

Video tutorials & articles, and templates to help you work better in Excel

      Click to view

 

Video Tutorials - Excel Formulas

Video Tutorials - Excel Formulas

Learn and work with new Excel formulas to help create effective, automated spreadsheets

Click to view

Excel Formulas: Circular reference warning

Excel Formulas: Circular reference warning

See what a Circular Reference warning means and how to locate it in your Excel spreadsheet

      Click to read more

 

Excel Formulas: Calculate difference in dates

Excel Formulas: Calculate difference in dates

Learn how to calculate the difference between two dates in your Excel spreadsheet in a few easy steps

      Click to read more

Excel Formulas: Why a formula doesn't work

Excel Formulas: Why a formula doesn't work

Find out why your formula might not work in Microsoft Excel and how to fix it

      Click to read more

 

Excel Formulas: How to work out gross profit

Excel Formulas: How to work out gross profit

Find out how to use a formula to work out gross profit in Microsoft Excel

      Click to read more

GET IN TOUCH

Call us on:
0333 6000 111

   You can Email us direct (You'll usually get a response within 2-3 working hours)
   Or fill out the details below and we'll be in touch very soon:
SEND MESSAGE

WHAT IS THE DATEDIF FUNCTION

We can enhance your skills with courses for Microsoft Excel spreadsheets at any of our twelve training centres UK wide, or we can come to your offices: if you have groups of three or more staff who need training, this is a very convenient, flexible and cost effective option. Moreover, most of our excel courses are 1 day, which keeps "out of office" periods for attendees to a minimum.

We have been delivering Microsoft Excel course all over the UK since 1995 and have continually developed our philosophy of high quality, flexible and results-driven provision of courses for Excel to ensure constantly improving standards of quality.   So you can be confident that we have the expertise, and the experience, to give you a great return on your investment in Free Video Tutorials : Excel Formulas.

Why not contact us today to see how we can help with providing your MS Excel classes ?


Stay in touch

Stay In Touch!

Google+
LinkedIn
Twitter
FaceBook
YouTube

Contact Details

Activia Training

Locations Address: Head Office, Regus House,
268 Bath Road
Slough, Berkshire SL1 4DX

Phone Sales:           0333 6000 111

Service:       0333 6000 555

Accounts:   0333 6000 777



Back to top