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.
If you go to Formulas >> Function Library >> Date & Time you can see that there’s no such function as DATEDIF.
There is no help from Excel there. But if you know the parameters, you can enter them and it will work.
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).
|
1 |
=DATEDIF(B2,B3,"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.
| Start | End | Difference | Formula | Returns |
| 2014-01-01 | 2022-02-25 | 2977 | =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.
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.
