SOLVED

Age calculation

Copper Contributor

Age calculation

Hi

We have a list where there is an age recorded, usually either years, months or weeks.  Is there a formula that can use the age, the date its recorded and then calculate the age in yr/mths/wks as of the current date?

eg Column A contains the first age recorded and Column B has the date it was recorded.  I want to calculate the current age according to the current date in Column C. Is this possible?

 A B C D 1 9 wks 23/09/2023 2 2 yrs 15/10/2023 3 4 mths 01/06/2023

I have seen ones based on date of birth and years/months/days.

Thanks in anticipation!

best response confirmed by Debbie3103 (Copper Contributor)
Solution

Re: Age calculation

First, you might want to split the age and the units into two separate columns for easier calculation. If you're okay with adding an extra column, say Column D for age units (wks, yrs, mths), and Column E for the numeric value of the age, that'd make the formula simpler. But if not, you’ll need to use a combination of LEFT, MID, RIGHT, SEARCH functions to extract the number and unit from Column A.

Once you have the unit and the number separated, you can use a formula that checks the unit and calculates the age accordingly. Here’s a conceptual formula you can adapt:

``````=IF(D1="yrs", TODAY()-DATE(YEAR(B1)+LEFT(A1, SEARCH(" ", A1)-1),MONTH(B1),DAY(B1)),
IF(D1="mths", TODAY()-DATE(YEAR(B1),MONTH(B1)+LEFT(A1, SEARCH(" ", A1)-1),DAY(B1)),
TODAY()-DATE(YEAR(B1),MONTH(B1),DAY(B1)+LEFT(A1, SEARCH(" ", A1)-1)*7)))``````

You’ll need to replace D1 and A1 with the actual references to your units and numbers, and this formula assumes you're working with a clean split of units and numbers. It checks if the unit is years, months, or weeks and then calculates the current age accordingly by adding the appropriate number of years, months, or weeks to the recorded date and then finding the difference from today's date.

1 best response

Accepted Solutions
best response confirmed by Debbie3103 (Copper Contributor)
Solution

Re: Age calculation

First, you might want to split the age and the units into two separate columns for easier calculation. If you're okay with adding an extra column, say Column D for age units (wks, yrs, mths), and Column E for the numeric value of the age, that'd make the formula simpler. But if not, you’ll need to use a combination of LEFT, MID, RIGHT, SEARCH functions to extract the number and unit from Column A.

Once you have the unit and the number separated, you can use a formula that checks the unit and calculates the age accordingly. Here’s a conceptual formula you can adapt:

``````=IF(D1="yrs", TODAY()-DATE(YEAR(B1)+LEFT(A1, SEARCH(" ", A1)-1),MONTH(B1),DAY(B1)),
IF(D1="mths", TODAY()-DATE(YEAR(B1),MONTH(B1)+LEFT(A1, SEARCH(" ", A1)-1),DAY(B1)),
TODAY()-DATE(YEAR(B1),MONTH(B1),DAY(B1)+LEFT(A1, SEARCH(" ", A1)-1)*7)))``````

You’ll need to replace D1 and A1 with the actual references to your units and numbers, and this formula assumes you're working with a clean split of units and numbers. It checks if the unit is years, months, or weeks and then calculates the current age accordingly by adding the appropriate number of years, months, or weeks to the recorded date and then finding the difference from today's date.