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.
5 Responses to “Show more of your workbook on screens [quick tip]”
In 2013 you can also add to the QAT the hidden command "Toggle Full Screen View".
Instead of using the shortcut CTRL+F1, I prefer just to double-click one of the tab names (ie double-click the "Home" text on the Home tab) to enable the Ribbon Outline view. To return to the normal Ribbon state, just double-click your mouse again!
press Ctrl+Shift+F1, you will get a full screen
Instead of Ctrl + F1, I use Ctrl + Shift + F1 (Excel 2016)
Alt W E sequence for full screen
ESC to get back