One of the fundamental questions that Excel users may find themselves asking is “How to calculate the number of years, months, or days between two given dates?” This article will show the ways or methods in calculating days, months or years between 2 given dates.
There are certain situations when you want to know the number of days, months or years. For instance, in computing rentals or amortizations, in comparing usage or consumption, and many other scenarios, knowing how to calculate the days, months and even years will be vital. With MS Excel, it is very easy.
Calculating days
Step 1: In cell A1, enter =Today(). That should be a dynamic data showing the current date.
Step 2: In cell A2, enter a past date, like few days ago.
Step 3: In cell A3, enter =A1-A2
Step 4: The results shall be the number of days between the dates in A1 and A2.
NOTE: If the results of the formula above is a strange date, please format the cell and make it “General” format. Right click on the A3, then click Format Cell. Click the Number tab then select General.
Calculating Months and Years
Using the simple subtraction between dates, one can easily calculate the days between dates. However, calculating months and years are different.
Step 1: Change the value of cell A2 to May 5, 2011. You will notice that the Total days will recalculate.
Step 2: To calculate months and years, we will be using Excel’s DATEDIF() function. The syntax is =DATEDIF(startdate,enddate,”interval”). Here, the interval must be in the following interval codes (with quotes):
- “d”
- “m”
- “y”
- “ym”
- “yd”
- “md”
Step 3: The “ym” interval code yields the number of months between the two dates as if they were in the same year and ignores the year. The “yd”, and “md” interval codes yields the number of days between the two dates as if they were in the same year and ignores the year.
Step 4: Thus, in our example, in cell A4, enter the formula =DATEDIF(A2,A1,”m”) to calculate the number of months. Please notice that first parameter which is the “startdate” must be less than or equal to “enddate”. If they are reversed, you will get an error.
Step 5: To calculate the number of years, in cell A5 enter the formula =DATEDIF(A2,A1,”y”)
You can download the sample file below.