Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.

In the image below we can see all the formulas that we will review in this post.

We have entered a birthday in cell A2. Cell B2 contains the TODAY function which returns the current date.

We can use the DATEDIF function to calculate the years, months and days.

The DATEDIF function is a little unusual in terms of Excel functions. It doesn’t have any “intellisense” built around it, so you must create the function from scratch without any assistance.

The DATEDIF function requires a start date and an end date. Then you need to provide a text code that determines the type of calculation that is performed.

If you use the “y” code DATEDIF returns the number of full years between the two dates.

Using the “ym” code returns the number of completed months after the completed years have been calculated.

Using the “md” code returns the number of completed days after the completed months have been calculated.

Those three codes have been used in cells B4, B5 and B6 respectively.

LET function

To combine all the numbers and add some text we will use the new LET function.

The formula is in cell B8. You can see the formula displayed in cell B9 going across the page.

The LET function allows you to use variables within your formulas. In this case we have a variable called s which we use to capture the start date. And a variable called e that we used to capture the end date.

After you have captured the variable values you can then perform calculations using those variables. The three separate DATEDIF functions have been combined with some text to create the final output. Note the & symbol joins text together. Also note that there are spaces within the text strings.

LAMBDA function

The LAMBDA function is another new function. This function also allows the use of variables. This function works with range names to allow you to create custom functions. I have done a previous post on custom functions which goes into more detail you can see that post at the link below.

Avoid the #DIV/0! Error in Excel

LAMBDA functions that are to be used as a custom function using a range name you need to test the formula on the sheet. This is not how you use the function, but you do need to test it this way. The LAMBDA formula in cell B11 is shown in cell B12.

This particular LAMBDA requires two inputs, the start date and the end date. The LAMBDA function has a unique structure when you’re testing. It has a separate set of brackets on the end which provide the inputs to the LAMBDA function. In the case of cell B11 the inputs are on the end of the LAMBDA function. They are (A2,B2).

We are working with age and most people want to find out their age at today’s date. This means we can modify the LAMBDA function so that you only need to provide the start date (birthdate). When just the start date is provided we will use the TODAY function to calculate today’s date. That LAMBDA function is in cell B14 and the formula is shown in cell B15. Note there is only one cell reference in the brackets at the end.

When you are using an optional argument then you need to enclose that argument in the square brackets. You also then need to use the ISOMITTED function in an IF function to identify if that argument has been included.

The custom function is used in cell B17. It returns the same as the formula in cell B14. The custom function formula is shown in cell C17.

To convert the LAMBDA function from cell B14 into a custom function we need to create a range name.

Copy the LAMBDA function as per below.

=LAMBDA(start,[end],LET(s,start,e,IF(ISOMITTED(end),TODAY(),end),DATEDIF(s,e,"y")&" years "&DATEDIF(s,e,"ym")&" month(s) "&DATEDIF(s,e,"md")&" day(s)"))

This doesn’t include the (A2) entry on the end.

Click in the Formulas tab and then click the Define Name icon. The image below has the completed New Name dialog.

Paste the LAMBDA formula into the Refers to box and change the name to fnAGE.

I have recently been prefixing all my custom functions with fn so that they are all listed together in the Name Manager. Also when creating a formula, when you type in fn all the custom functions will be listed. This also differentiates the custom functions from Excel’s built-in functions.

In the Comment section of the New Name dialog I have created some documentation that assists in the creation of the formula. This documentation will display when you type in the function name in the formula bar.

You can download the example file at the button below.

10 January 2023

Thanks to Rick Rothstein MVP for a tweak to handle plurals. Rick left a comment with an alternative LAMBDA. I have replicated it below. This is ready to paste in to the Refers To box of the New Name dialog.

Thanks Rick

=LAMBDA(start,[end],LET(s,start,e,IF(ISOMITTED(end),TODAY(),end),y,DATEDIF(s,e,"y"),ym,DATEDIF(s,e,"ym"),md,DATEDIF(s,e,"md"),y &" year"&IF(y=1," ","s ")&ym &" month"&IF(ym=1," ","s ")&md &" day"&IF(md=1," ","s ")))