fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn
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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  1. Mark Walker says:

    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"))))

  2. Haz says:

    Excel 2007+ has a function YEARFRAC() that can the difference using basis such as 30/360

    =YEARFRAC(Start_date,End_date,4)

    You can truncate it when you want just the years or multiply by 12 to get the months, etc.

Leave a Reply