If you need to know the number of days between two dates, Excel makes it quite simple. Simply divide one date by the other. For example, if cell A2 contains an invoice date of 1/1/2015 and cell B2 contains a paid date of 1/30/2015, you may use the formula =B2-A2 to calculate the number of days between the two dates, which is 29. Excel may format the cell containing the formula as a date, which means that instead of 29, you’ll see 1/29/1900. To make the result appear as 29, simply change the formatting for that cell to General or any number format (right-click > Formatting).
Things get difficult if you want to know how many months or years there are between two dates. The most efficient method is to use the DATEDIF() function. This one isn’t in the function wizard, so you’ll have to remember how to write it in manually. According to the explanation I received, it isn’t a typical Excel feature and is only added for Lotus 1-2-3 compatibility.
The function’s format is DATEDIF (start date, end date, unit).
The start and end dates are self-evident. The unit of measure, which can be “D”, “M”, or “Y” for days, months, or years, respectively. There are three more unique alternatives, which are explained in the following example.
Sample formulas for each of the unit possibilities are shown in the table below. Column D shows the formula that was used to produce the result given in column C . It’s worth noting that the answer only displays whole units. For instance, even if the two dates are a little more than 5 years apart, the calculation on row 4 displays 5 years. The function is not circular. The answer would still be 5 if the two dates were 5 years and 364 days apart.
| A | B | C | D | E | F |
| 1 | START DATE | END DATE | DIFFERENCE | FORMULA | UNIT – DIFFERENCE CALCULATED IN.. |
| 2 | 01/01/2010 | 02/18/2015 | 1874 | =DATEDIF(A2, B2,” D”) | Days (gives the same result as =B2-A2) |
| 3 | 61 | =DATEDIF(A2,B2,”M”) | Months | ||
| 4 | 5 | =DATEDIF(A2,B2,”Y”) | Years | ||
| 5 | 17 | =DATEDIF(A2,B2,”MD”) | Days, ignoring month and year (days from 1st to 18th) | ||
| 6 | 1 | =DATEDIF(A2, B2,” YM”) | months, ignoring year (months from Jan to Feb) | ||
| 7 | 48 | =DATEDIF(A2,B2,”YD”) | days, ignoring year (days from 1/1 to 2/18) |
