Forum Discussion

Debbie3103's avatar
Debbie3103
Copper Contributor
Nov 22, 2023

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 ...
excel
Formulas and Functions
ATjunior's avatar
ATjunior
Copper Contributor
Nov 22, 2023

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.

Resources