Age calculation

[Debbie3103]

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!

[#2 · Accepted solution]

@Debbie3103 

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.