All aspects of life revolve around time. All daily activities we do in our life also depend on time. We all need to keep track of time because it is temporary. Time is measured or accounted for in seconds, minutes, hours, days, weeks, months, and years.
To calculate years between years is the general act of getting the number or the value in years between two given dates. We can get the difference between the dates or manually count the number of months involved and then translate the months to years.
In excel sheets, we also calculate the years in the middle of two dates or even three dates. Unlike manual counting, excel has these built-in formulas that help calculate the years.
We have some steps to follow to reach a value of the years between the given dates. These steps will include the following and are explained below with examples.
You can calculate the years between two dates using four major functions as outlined below:
Using the DATEDIF function.
The DATEDIF function is the easiest formula to determine the difference in years between two dates in excel. It is represented by the syntax DATEDIF (start_date, end_date, unit), where;
start_date represents the first date, often known as the commencement date.
end_date represents the current date or the end of the period.
unit represents the information you would like to get.
To calculate the age for a particular period using this method, you need to record the first date in your dataset and the end date. From there, you can proceed with the following steps to calculate the years between the two dates:
1. Select the cell where you want to calculate years. For example, you can select Cell J5.
2. Type the formula on the selected cell. Ensure the unit is “y” because you want the difference in years.
3. Press the OK button and the formula will show in the formula bar.
4. Drag the Fill Handle down to copy the formula over the dataset.
6. Finally, you will have the difference in years calculated in the cell.
Using YEARFRAC Function
The syntax for this formula is YEARFRAC (start_date, end_date, [basis]), where;
start_date is the start date.
end_date is the current or the termination date as represented by the data.
[basis] is the day counting criterion to be used (optional).
To calculate the years between dates using this formula, follow these steps:
1. Select the cell to show the calculated years.
2. Write the formula in the selected cell and press Enter to display the formula in the formula bar.
3. Drag the Fill Handle down to copy the formula over the dataset and you will see the calculated years in each cell.
Using Excel LET Function
The LET function allows you to calculate years and months between two dates. Its syntax is LET (name1, name_value1, [name2], [name_value2], …, calculation), where;
name1 is the first name assigned and should start with a letter.
name_value1 is the name1’s assigned value or calculation.
[name2]/ [name_value2] is the second name assigned and its worth.
calculation is the calculation that assigns names and values. This is optional.
To calculate the difference in years and months between two dates using this formula, follow these steps:
1. Select the cell to show the calculated years and months.
2. Insert the formula in the selected cell.
3. Click OK and the cell will appear in the formula bar.
4. Drag the Fill Handle down to copy the formula over the range. The difference in years and months will appear in the resulting cell.
Using Excel VBA
Excel VBA also allows you to perform various activities, including calculating the difference in years between two dates. To use the VBA Code to calculate years; follow these steps:
VBA Code Source: exceldemy
Sub Years_and_Months_Between_Two_Dates()
Dim StartDate As Date, EndDate As Date
Dim year As Integer
Dim month As Integer
StartDate = "1-Jan-2020"
EndDate = "15-Mar-2022"
year = DateDiff("yyyy", StartDate, EndDate)
month = DateDiff("m", StartDate, EndDate)
MsgBox "Number of years: " & year, vbInformation, "Years Between Two Dates"
MsgBox "Number of months: " & month, vbInformation, "Months Between Two Dates"
End Sub1. Go to the Developer tab on the ribbon.
2. Click on the Visual Basic option or press Alt + F11 to open the Visual Basic Editor.
3. You can also open the Visual Basic Editor by right-clicking on the sheet and selecting View Code.
4. When the editor opens, write down the VBA Code
5. Run the code by pressing the F5 key or clicking the Run Sub
6. When the first Message Box shows the number of years, click OK, and you will see the number of years and months between two dates.
How to calculate the difference of full years between two dates
Assuming you have the data set already in the excel sheets, you do not have to redo the first step again. For those doing it for the first time, open the excel from your laptop, create a new or an empty excel sheet, and record the date values of your choice as in the case below.
To get the years between the dates on the above excel sheets, we are going to use the function YEARFRAC. This is the function that calculates the years between dates. One thing about the YEARFRAC function is that it can calculate even in decimal. For instance, it can display 1.5 years to mean one and a half years.
On the column with the header, years are where we expect to record our results. Enter the following formula in the formula bar =YEARFRAC (A2, B2) to calculate the years between the dates in cell A2 and cell B2.
Use the same formula for the rest of the cells or hold and drag the cursor to calculate the rest of the years automatically.
The above formula calculates the years as decimals in case it's not full. But sometimes, you may be looking to calculate full years only. In this case, we are going to use the function DATEDIF(Start date, end date, "y")
For example, let's use the same dates as above:
1. Type =DATEDIF(A2, B2,"y") on the cell that you want to display the results and press Enter.
2. Drag the formula to calculate the year difference of the remaining cells
As you can see, this function has calculated only the full years.
How to calculate the difference in months
To calculate the difference between two dates in months, we use the function =DATEDIF(start_date,end_date,"m")
m in this function represents a full month
Consider the following methods;
Using YEARFRAC and INT or ROUNDUP Functions
The YEARFRAC in this case returns the year decimals to represent whole days between start_date and end_date.
1. Type =INT (YEARFRAC (H4, I4) *12) in Cell J4 and press Enter.
2. Using the Fill Handle, find out the number of months as the period for the rest of the cells.
3. Using the INT function at the initial stage will not round off the decimal value. Instead, it will omit the decimal even if the value is close to the next whole number.
4. Contrary, the ROUNDUP function will round up the number to a fixed decimal place or nearest whole number.
Combining YEAR and MONTH Functions
The YEAR function in this method returns the year of a date to an integer in the range 1900-9999, while the MONTH function returns the month to a number from 1(January) to 12(December). To calculate the difference in months using this method;
1. Type = (YEAR (I5)-YEAR (H5))*12+MONTHS(I5)-MONTHS (H5) in Cell J5
2. Press Enter and autofill the whole column using the Fill Handle.
3. The procedure allows you to find the difference between years, convert years into months, and add differences between ranks or orders of two months.
How to calculate the difference in weeks
The formula for calculating the difference between two dates in weeks is: =DATEDIF(Start_date,End_date,"d")/7
"d" represents the number of days while "/7" converts the days to weeks
How to calculate the difference in days
Using the ROUNDDOWN Formula
The formula works better if the start date is later than the end date, although you will get a minus sign before the final calculation. To remove the minus sign, you can use the ABS function, which turns the formula to
=ABS(ROUNDDOWN((C3-B3)/7))
To use this formula, follow these steps;
1. Subtract the dates indicated in C3 and B3 (C3-B3).
2. Divide the result by 7. That is because a week has 7 days.
3. Round the number down.
Using the Excel Formula
The Excel Formula allows you to calculate the number of weeks between two given dates by subtracting the dates in B3 from C3. The formula in cell D3 will be
=INT((I5-H5)/7)&”weeks”&MOD(I5-H5,7)&”days”
Here are the steps to calculate the difference in weeks using the formula:
Determine the difference in days between dates in cells I3 and H3 (I3-H3).
1. Calculate the number of weeks using the division character in the formula
2. Remove decimals. The INT function automatically removes the decimal part from the positive values and returns the whole numbers (integer). Only negative values are rounded down to the nearest integer.
3. Concatenate number and string using the ampersand character. Use double quotes with text values to avoid formula errors.
4. Use the MOD function to calculate the remainder after dividing a number by a divisor.
5. Concatenate numbers and text values using the ampersand character.
How To Calculate the Difference in Days Between Two Days
Subtraction Method
1. Select Cell 5 and type =I5-H5
2. Click Enter and you will see the number of days between the Launch and Closing Dates for your project
3. Point the cursor onto the right bottom corner of Cell J5 and you will see the Fill Handle or +
4. Click on the icon and drag it to Cell J8.
5. You will see the total days for all projects at once
Inserting the Days Function
Using the Days Function in excel, you can calculate the number of days between two days in these simple steps;
1. Type =DAYS(I5,H5) in Cell J5
2. Press Enter and use Fill Handle to find other results in the column
3. You will, however, need to input Start and Close Dates as arguments when using this formula.
Using the NETWORKDAYS Function
The method allows you to include weekends and holidays when calculating the difference in days between two dates. To calculate the total working days, follow these steps:
1. Type =NETWORKDAYS (H5, I5, $H$11: $K$12) in Cell J5 and click OK.
2. In this case; H5 and I5 represent the start date and end date respectively. $H$11: $K$12 represent the selected holidays from the assays (H11:K12) and locking the Row Numbers and Column Numbers with the $ symbol makes them absolute cell references. You will need to input those exact holidays for all projects.
3. Use the Fill Handle to autofill other cells.