How to calculate number of years, months, days in excel between two dates?
If you need to calculate the time difference between two dates in Excel, including the number of years, months, and days, follow the step-by-step guide below.
For example:
Given:
- Start Time: 1991-11-01
- End Time: 2022-02-20
Step-by-Step Guide:
1. Enter Data in A2 and B2 Cells:
First, enter the two dates into Excel:
- A2 (Start Time): 1991-11-01
- B2 (End Time): 2022-02-20
2. How to Calculate number of years between two dates in Excel?
Method 1: Using DATEDIF
(Whole Years Difference)
If you only want to calculate the number of complete years (without decimals), use DATEDIF
:
Formula:
=DATEDIF(A2, B2, “y”)
Result: 30
Explanation:
The difference or number of years between 1991-11-1 and 2022-02-20 counts full 30 years.
Method 2: Using YEARFRAC
(Precise Year Difference with Decimals)
If you want to calculate the precise number of years (including decimals), use YEARFRAC
:
=YEARFRAC(A2, B2)
Result: 30.30
Explanation of 30.30
- The decimal part represents the fraction of a year.
0.30
of a year is approximately 3 months (since 0.30 × 12 months ≈ 3.6 months).- Therefore,
30.30
means 30 full years and about 3 additional months. - which is 30 years and 3 months.
3. How to Calculate number of months between two dates in Excel?
If you want to express the difference between two dates 1991-11-1 and 2022-02-20 in months, you can use the following methods:
Method 1: Using DATEDIF
(Whole Months Difference)
Formula:
=DATEDIF(A2, B2, “m”)
Result: 363
Explanation:
The difference or number of months between 1991-11-1 and 2022-02-20 counts total of 363 full months.
Method 2: Using YEARFRAC
(Precise Month Difference with Decimals)
If you want a more precise month difference, including decimals, you can use YEARFRAC
multiplied by 12
:
Result: 363.63 (meaning 363 months plus a few extra days)
Explanation of 363.63:
- 0.63 moths, which is approximately 19 days (since
0.63 × 30 ≈ 19
). - So
363.63
months ≈ 363 full months and 19 extra days. - which is 363 months and 19 days
4. How to Calculate number of days between two dates in Excel?
If you want to express the difference between two dates 1991-11-1 and 2022-02-20 in days, you can use the following methods:
Method 1: Direct Date Subtraction
In Excel, you can simply subtract the dates to get the difference in days:
=B2 – A2
Result: 11069
Explanation:
The difference or number of months between 1991-11-1 and 2022-02-20 is 11069 days.
Method 2: Using DATEDIF
for Day Difference
You can also use the DATEDIF
function to calculate the difference in days:
=DATEDIF(A2, B2, “d”)
Result: 11069.00 (meaning 11069 days)
5. How to Calculate years, months and days between two dates in excel?
Formula:
=DATEDIF(A2, B2, “y”) & ” years ” & DATEDIF(A2, B2, “ym”) & ” months ” & DATEDIF(A2, B2, “md”) & ” days”
Result: 30 years 3 months 19 days
Explanation:
The difference or number of years, months and days between 1991-11-1 and 2022-02-20 is 30 years 3 months 19 days.
Summary:
Start Time: 1991-11-1
End Time: 2022-02-20
The calculated differences:
Years: 30 year
Months: 3 months
Days: 19 days
Or you can use our date and time calculator to recheck the result.