Calculate the Amount of Time Between Two Dates in Excel

If you want to calculate the time between two dates, you have to remember that’s a bit more complicated than calculating the difference between two numbers.

The things that we need to take into consideration are a different number of days in months, or that February is a leap month. Other factors also must be taken into consideration.

Fortunately, Excel can make it much easier with functions that calculate the time between dates.

DATEDIF

When you start typing the function name, you will notice that it doesn’t show up from the available options.

Table, Excel

Description automatically generated

If you go to Formulas >> Function Library >> Date & Time you can see that there’s no such function as DATEDIF.

Graphical user interface, application, table, Excel

Description automatically generated

There is no help from Excel there. But if you know the parameters, you can enter them and it will work.

Graphical user interface, application, table, Excel

Description automatically generated

In the following example, the first and second arguments are the start and end dates. The third one is the unit. In our case, it’s a day (D).

You can also choose M for months and Y for years.

DATEDIF function examples

This is an example with additional units, where you ignore part of a date.

StartEndDifferenceFormulaReturns
2014-01-012022-02-252977=DATEDIF(A2,B2,”D”)days (same as B2 – A2)
  97=DATEDIF(A2,B2,”M”)months
  8=DATEDIF(A2,B2,”Y”)years
  24=DATEDIF(A2,B2,”MD”)days (ignores months and years)
  1=DATEDIF(A2,B2,”YM”)months (ignores years)
  55=DATEDIF(A2,B2,”YD”)days (ignores years)

Arithmetic operators

As I wrote at the beginning, math on dates it’s more complicated than on numbers, but it doesn’t mean that you can’t use standard arithmetic operators to calculate the difference.

Let’s see how it looks in our example.

Graphical user interface, application, table, Excel

Description automatically generated

The result is the same as with the DATEDIF function, but if you switch B2 with B3 values, you are going to have a negative number of operators and errors for the function.

Graphical user interface, application, table, Excel

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.