Let’s say you have two dates in the cells D4 & D5 as above. You want to find out the duration in years, months & days between both. We can use the good-old DATEDIF formula for this.
'Years:
=DATEDIF($D$4,$D$5,"y")
'Months:
=DATEDIF($D$4,$D$5,"ym")
'Days:
=DATEDIF($D$4,$D$5,"md")
'All in one formula:
=DATEDIF($D$4,$D$5,"y")&" years "&DATEDIF($D$4,$D$5,"ym")&" months "&DATEDIF($D$4,$D$5,"md")&" days"
Using LAMBDAs to create a custom function for this
We can use Excel’s LAMBDA functions to create a custom duration function to get the duration in years, months & days from any two dates.
This feature is available with Excel 365.
Here is one such formula.
=LAMBDA(start,end, LET(y, DATEDIF(start,end,"y"), m, DATEDIF(start,end,"ym"), d, DATEDIF(start,end,"md"), y &" years "&m&" months "&d&" days"))
Setting up this LAMBDA getDuration() Function in your Excel
To use this LAMBDA function in your Excel,
- Go to Formula Ribbon > Name Manager
- Click on New Name
- Type the name as getDuration
- Type Refers to as the above LAMBDA function
- Click OK
Now, you will have a getDuration() function in your Excel!
Calculating Other Types of Durations in Excel
Refer to below formulas to calculate other kinds of duration in Excel. All of these formulas assume you have start & end dates in cells D4 & D5.
Duration in DAYS
=D5-D4
Duration in WORKING DAYS (Monday to Friday)
=NETWORKDAYS(D4,D5)
Duration in WORKING DAYS (Custom Weekend)
If you have a custom weekend type (for example, Friday, Saturday weekend), you can use the NETWORKDAYS.INTL function like this to calculate the duration in working days.
Both of these functions (NETWORKDAYS, NETWORKDAYS.INTL) also accept an optional list of holidays (such as Christmas, New Years day etc.) to handle arbitrary holidays.
=NETWORKDAYS.INTL(D4,D5, 4)
'calculates working days between D4 & D5 with Tuesday, Wednesday weekend policy!
Number of Months
=DATEDIF(D4, D5, "m")
Duration in Years
=(D5-d4)/365.25
'calculates the duration in years with leap year logic.
Try these formulas yourself
Click here to download the SAMPLE FILE with all these duration calculations.
To use the LAMBDA function, you need Excel 365.
More on Duration Calculation in Excel
Check out below articles to learn more about Date calculations like this.
One Response to “How to calculate time between two dates in Years, Months & Days [Excel Formula]”
I personally don't like it when there are zeros in each time frame. I.e., for example 0 years 11 months 2 days etc
I expanded your lambda to the following instead:
LAMBDA(start,end,LET(y,DATEDIF(start,end,"y"),m,DATEDIF(start,end,"ym"),d,DATEDIF(start,end,"md"),TEXTJOIN(" ",TRUE,IFS(y=0,"",y=1,y&" year",y>1,y&" years"),IFS(m=0,"",m=1,m&" month",m>1,m&" months"),IFS(d=0,"",d=1,d&" day",d>1,d&" days"))))