In this article, we will be discussing how you can calculate the total number of days, months, and years between two dates. Keep reading this article to learn how to save time using formulas the next time you calculate dates!
Before We Begin
You must remember a few things before calculating differences between dates in Excel. As you will be using a function to calculate data, even minor mistakes can cause errors in your calculations. Before you start your calculation, remember to check the following:
The cell format is set to General.Show Formula is disabled from the Formulas tab.The dates you’ve entered are according to your computer’s date format.The comma (,) symbol is set as the list separator in your computer.
How to Calculate Days Between Two Dates in Excel?
To calculate the days between two dates, you must learn how to use the DATEDIF formula. The DATEDIF formula uses the value in the referred cell to return a value.Depending on if you’re calculating the difference in days, months, or years, you will have to add the arguments d, m, or y, respectively, at the end of your formula. The letter you choose will determine the format in which the result will be displayed.Below, we’ve listed the labels for each item in the formulas listed in the methods:
cell1: Prior datecell2: Ending date
Calculate the Difference in Days
If you want the result to display in the total number of days, you will need to add the d argument at the end of your formula. This method is suitable for dates with fewer days in between. Although you can still use this for larger values, the result may be difficult to comprehend.Follow this format to calculate the differences in days:=DATEDIF(cell1, cell2, “d”)The DATEDIF function will use the values from the referred cells and represent the values in the total number of days in between.
Calculate Days in Weeks
If you’re dealing with dates that are a bit too large to represent in days, you can represent them as weeks. You can use the same formula for calculating days and divide it by 7.As a week contains seven days, the result will be displayed in weeks.Here is the format to present the number of days in weeks:=DATEDIF(cell1, cell2, “d”)/7
Calculate Days in Months
For even larger values, you can calculate days in the month format. To calculate your data in the month format, you need to add the letter m as an argument at the end of your formula. Follow this format to calculate the days between two dates in months:=DATEDIF(cell1, cell2, “m”)The function will extract data from the referred cells to calculate the difference in months.
Calculate Days in Years
You can also count the days between two dates in years. Add the argument y at the end of the DATEDIF formula to prompt Excel to calculate years in between two dates. Refer to this format to calculate days between dates in years:=DATEDIF(cell1, cell2, “y”)The DATEDIF function will use the data from the cell referred to in the formula and present the days between them in years.
Calculate Age in Years, Months, and Days
Apart from calculating the number of days between two dates, you can also the age between two timelines. You can calculate the age of someone or something using the DATEDIF function for years and months. The process is a bit tedious for counting days.We will not be covering the process of calculating age in years here as the process is identical to calculating days in years. Below, we have listed how you can calculate the number of months and days from the recent year.
Calculate Past Months
Use the DATEDIF function to determine the number of months from the past year. To find the value of months, you need to add the ym argument at the end of the formula. Follow this format to calculate the months:=DATEDIF(cell1, cell2, “ym”)
Calculate Past Days
Calculating the number of days passed in a year can be a bit tricky. Although you can use the MD argument in the DATEDIF formula, Microsoft Excel doesn’t recommend it as it may pass incorrect results. Instead, you can subtract the years and months from the total date that includes the remaining days. This way, Excel will subtract the years and months and return the number of remaining days.Here is the format to calculate the days from the past year:=cell2 – DATE(YEAR(cell2), MONTH(cell2), 1)If you want to display the full age of something or someone, you can enter all three formulas in a cell, separating them by the Ampersand operator.
