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 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?

 ABCD
19 wks23/09/2023  
22 yrs15/10/2023  
34 mths01/06/2023  

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

Thanks in anticipation!

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