Microsoft Excel – number of days between two dates

You want to calculate a person’s age, or figure out how many shopping days there are until Christmas.

Type the two dates into cells in a workbook. If you want to use today’s date then use the formula =TODAY(), which updates automatically each time a workbook is opened.

Click in the cell where the result is to go.

Type an = sign, as all formulae start with an equal sign. Type DATEDIF followed by an opening bracket, (. Now type in the cell reference of the oldest date, in this case B3, or click on the cell the mouse. Follow this with a comma and the cell reference of the nearest date, in this case B10. Follow this with another comma and finish the formula entry by typing the letter d between double quotes and closing the brackets.

Press the Enter key, or click the tick on the formula bar to accept the formula.

The number of days between the two dates is shown in the cell.

The DATEDIF is a function not publicised by Microsoft, but is very useful. There is no reference to it in the help files, and it does appear in the Formula Autocomplete list. DATEDIF can also calculate the number of months, or years, between dates by using “m” or “y” rather than “d” as the last argument of the formula.

In the example shown, Peter’s date of birth, B13, is the first argument, followed by the current date, B3.

If you are using Microsoft Excel 2013 or Microsoft Excel 2016, you can use the new function DAYS. This works as follows:

=DAYS(end date, start date) and will return the  answer in days.


Find more information about our Microsoft Excel training Sydney, Australia.

Find more information about our Microsoft Excel training London, UK, or our Microsoft Excel training Hampshire, Wiltshire, Dorset.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , , . Bookmark the permalink.

One Response to Microsoft Excel – number of days between two dates

  1. Thank you Janet, This a valuable formula to use. Previously when I needed to find employees length of service, I had to use 3-4 formula to calculate it. Now I know how easy is it is “if only I’d known this”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s