Calculate the difference between two dates

There are different ways to calculate the number of days (or weeks and months) between two dates. The table below shows common methods using the date and time functions.

To use these examples in Excel, drag to select the data in the table, then right-click the selection and pick Copy. In a new worksheet, right-click cell A1 and pick Paste Options > Keep Source Formatting.

Data

10/2/2014

5/2/2014

6/3/2014

7/3/2014

Formula

Description

=A2-A3

Gives the number of days, 153, between the two dates in A2 and A3.

If cell doesn't display as a number, make sure it is formatted as a number or General. Learn how to format a cell as a number or date.

=("10/2/2014"-"5/2/2014")

Gives the number of days, 153, between two dates without using cell references.

=DAYS(TODAY(),"2/15/79")

Gives the number of days between two dates using two date functions.

The two arguments of the DAYS function can be actual dates, cell references, or another date and time function, such as the TODAY function.

=(YEAR(NOW())-YEAR(A3))*12+MONTH(NOW())-MONTH(A3)

Gives the number of months between A3 and the current date.

This example uses the YEAR function , NOW function, and MONTH function.

When you enter this formula into a cell, make sure you format it as a number. Learn how to format a cell as a number or date.

=NETWORKDAYS(A3,A2,A3:A5)

Gives the number of whole working days, 107, between two dates in A2 and A3. Working days exclude weekends and holidays. The last argument, A3:A5, lists the holidays to be subtracted from the working days.

This example uses the NETWORKDAYS function.

=DAYS360(A3,A2,TRUE)

Gives the number of days, 150, between two dates based on a 360-day year (twelve 30-day months) used in accounting calculations.

This example uses the DAYS360 function.

=EDATE(A3,-4)

Gives 1/4/2014, which is four months (or -4) prior to the date in A3.

This example uses the EDATE function, which is used to calculate maturity dates on banking notes.

Related

  • Learn how to calculate the difference between dates using functions from Google Sheets: DATEDIF, DAYS360, and EDATE.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Date and Time functions

Errors in worksheets, like #N/A, #REF, and #Name?

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Keyboard shortcuts in Excel for Windows

Keyboard shortcuts in Excel for Mac

Excel functions (alphabetical)

Excel functions (by category)

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×