How to calculate time between two dates in Years, Months & Days [Excel Formula]

time between two dates excel formula

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, 

  1. Go to Formula Ribbon > Name Manager
  2. Click on New Name
  3. Type the name as getDuration
  4. Type Refers to as the above LAMBDA function
  5. Click OK

Now, you will have a getDuration() function in your Excel!
 
To use it, just type=getDuration(start,end) in any cell.

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.

  • Add any number of days, months or years to a date with this formula
  • Highlight due dates, over due items in Excel
  • Calculating Duration in days, months & years – Excel Jet
  • DateDif documentation

The post How to calculate time between two dates in Years, Months & Days [Excel Formula] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/duration-between-two-dates-in-years-months-days-excel-formula/

Close Menu