Age of Child

[shah_ir85]

Hi all.

I have list data of child and i want to know their exactly age and yearly age

	A	B	C	D	E
1	Father	Child	Birth Date	Exactly Age	Yearly Age
2	John	Sam	1 Dec 20	3 Month 25 Day	1
3	Smith	Sunny	1 Jan 21	2 Month 25 Day	0
4	Atoi	Thia	31 Dec 09	11 Year 2 Month 26 Day	12
5	Hilton

Hilton not have child, so D5 and E5 will give 0 value and auto hide
but value on E3 will remain 0 because Smith's son was born Year 2021 so value of year will be 0
value on E2 will be 1 because John's son was born last year
i have long list... so if their not have child, D & E will be 0 and auto hide

 

[Misca]

Try:

Exactly Age =IF(C2="","",IF(--LEFT(TEXT(TODAY()-C2,"yymmdd"),2),--LEFT(TEXT(TODAY()-C2,"yymmdd"),2)&" years ","")&IF(--MID(TEXT(TODAY()-C2,"yymmdd"),3,2),--MID(TEXT(TODAY()-C2,"yymmdd"),3,2)&" months ","")&--RIGHT(TEXT(TODAY()-C2,"yymmdd"),2)&" days")

Yearly Age =IF(C2="","",YEAR(TODAY())-YEAR(C2))

If you had Excel 365 version you could use the LET function to make the Exactly Age function to look a lot cleaner but I used the old fashioned formulas that should work in any version. Depending on your language settings you might have to tweak the "yymmdd" part to match the years/months/days letters of your language.

 

[shah_ir85]

tQvm boss... u are great