expression to auto calculate current age ?...

MRK_Matrimony · 01-18-2018 04:00 PM

expression to auto calculate current age ?

Bellave_Jayaram

YEAR(TODAY())-YEAR([Date of Birth])

MRK_Matrimony

yes i did that but i get age 1 year elder

MRK_Matrimony

not happening. any example PLZ@Bellave_Jayaram

Bellave_Jayaram

Sorry, don’t have any example - I take back what I said about doing it directly as app formula in the AGE column. You

will need a VC since you need to compare TODAY() with a Date datatype.

MRK_Matrimony

Can i use this IF(MONTH(TODAY())>MONTH(Birthdate),YEAR(TODAY())-YEAR(Birthdate),IF(AND(MONTH(TODAY())=MONTH(Birthdate),DAY(TODAY())>=DAY(Birthdate)),YEAR(TODAY())-YEAR(Birthdate),(YEAR(TODAY())-YEAR(Birthdate))-1))

found it on google… can i use if expression with number value…??

Bellave_Jayaram

See this - plus.google.com - If anyone is looking for automatically calculate an age from a date… heres … If anyone is looking for automatically calculate an age from a date… heres … plus.google.com

Stephen_Mattiso

@Bellave_Jayaram Oh yea, that is a kickass post right there!

Got it working like a charm! I actually made all choices for month, day, and year into enum buttons to eliminate any typo errors. Sweet!

MRK_Matrimony

trying to search to match right age as per age this year before “birth day”

MRK_Matrimony

@Bellave_Jayaram

trying to search to match right age as per age this year before “birth day”

MRK_Matrimony

@Bellave_Jayaram

I also tried This =(YEAR(TODAY()) - YEAR([D.O.B])) - (MONTH(TODAY()) - MONTH([D.O.B]))

but this gives me age 2 years Elder

MRK_Matrimony

suppose the DOB is “02/02/1990” the age now is 27, but with

YEAR(TODAY())-YEAR([Date of Birth]) this expression it gives me output as “28”

where in 28 gets completed on 02/02/2018. @Bellave_Jayaram

Bellave_Jayaram

Make a VC for birthday this year and compare TODAY() with that Date. Use a formula for your VC and set it to Date datatype. Something like =CONCATENATE(MONTH([Date]),"/",DAY([Date]),"/",YEAR(TODAY())) Then use an IF formula to subtract or add 1 as needed.

MRK_Matrimony

okay will try

MRK_Matrimony

i want to enter a right AGE of person in Age column how will VC help me. any example can u display …

Bellave_Jayaram

You can either do the age calculation in the VC and set the AGE app formula as [VC] so it will get written to the sheet OR you could put the app formula directly in the AGE column.

Yobachus

so i tried to put a formula in the excel document i had my program linked to so that whenever i put in a new date of birth it would calculate the age. Didnt work, and for some reason it would recalculate every row to the same value no matter what change i synced.
I couldnt quite figure out using a dummy virtual column which is what i assume u meant by VC. BUT i found keeping the values in the excel without formulae in the sheet and instead using the formula below in the Data - Collumns - Age - Auto Compute - App Formula it worked like a charm. but of course doesnt allow user input and only calculates on sync. But that works for me. so thanks for the thread, even if it was a couple years ago
(YEAR(TODAY())-YEAR([D.O.B]))+((MONTH(TODAY()) - MONTH([D.O.B]))/12)

aegis

doesn´t work if birthday hasn´t passed

Steve · This? `(hour(today() - "1/2/1961") / 24 / 365)`

This?

(hour(today() - "1/2/1961") / 24 / 365)

aegis

thanks, I´ll try it…
for now I found a work around using weeknumber… it´s going to be unprecise but just one week a year!

IF(WEEKNUM(NOW()) > WEEKNUM([Birthday]),YEAR(NOW())-YEAR([Birthday]);YEAR(NOW())-YEAR([Birthday])-1)

aegis

thank you again, yours is way more elegant and precise!!

colt

I am not quiet sure but I think dividing by 365.25 should work even better (Leap year)

(hour(today() - "1/2/1961") / 24 / 365.25)

Pastor_Marlon

Does anyone know the formula to calculate the next birthdate?

redvines88

Maybe it's too late, but I have another solution, perhaps someone need it for the future..

I'm using this appformula in my VC :

concatenate(if(and(month([DOB])<=month(today()),day([DOB])<=day(today())),year(today())-year([DOB]),year(today())-year([DOB])-1)," years old")

you can skip the concatenate section if you don't need it.

Joseph_Seddik

This is my take:

YEAR([TODAY]) - YEAR([DoB]) -
IFS(
MONTH([TODAY]) = MONTH([DoB]), IF(DAY([TODAY]) >= DAY([DoB]), 0, 1),
MONTH([TODAY]) > MONTH([DOB]), 0,
1
)

SkrOYC

FLOOR(
  HOUR(TODAY()-[DoB])/24/365.25
)