Search

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

Share

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

To use the LAMBDA function, you need Excel 365.

## More on Duration Calculation in Excel

### 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

Excel School made me great at work.
5/5

– Brenda

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.

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.

### Sales Analysis Dashboards with Power BI – 30+ Alternatives

Do you need inspiration for your upcoming Power BI sales dashboard? Well, I got you covered. In this page, let me present 33 alternatives for Sales Analytics Dashboards with Power BI.

## Related Tips

Learn Excel

Excel Howtos

Learn Excel

Learn Excel

Excel Howtos

Excel Howtos

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.