So, I wanted to claculate age from user birthdate to current date in Google sheet in my expected format. I tried few formula's from some sources, but it is just not happening.
Can anyone please guide me?
For reference and test purpose, I'm attaching one Google sheet public link. No worries if email address will be shown in Googe sheet.
Link: https://docs.google.com/spreadsheets/d/1jRlr6A3YRJIo1Ah1TSlRsDcLEDV_2BJ8YaBRS3YVC6Q/edit#gid=0
Assume birthday date is placed in range 'A2'.
Current age:
=DATEDIF(A2,TODAY(),"Y")&" year(s) "&DATEDIF(A2,TODAY(),"YM")&" month(s) "&DATEDIF(A2,TODAY(),"MD")&" day(s)"
you could use:
=INDEX(JOIN(" ",DATEDIF(A2, NOW(),
{"Y", "YM", "MD"})&{" year(s)", " month(s)", " day(s)"}))
but to make it "smart" (not show null values and use plural only when needed) try:
=INDEX(JOIN(" ", LAMBDA(a, LAMBDA(x, IF(x=0,,IF(x>1, x&a&"s", x&a)))
(DATEDIF(A2, NOW(), {"Y", "YM", "MD"})))({" year", " month", " day"})))
and for array it would be:
=INDEX(IF(ISDATE_STRICT(A2:A), TRIM(FLATTEN(QUERY(TRANSPOSE(
IFERROR(LAMBDA(a, LAMBDA(x, IF(x=0,,IF(x>1, x&a&"s", x&a)))
(DATEDIF(A2:A, NOW(), {"Y", "YM", "MD"})))({" year", " month", " day"}))),,9^9))), ))
