Date calculation woes

Posted by Susan Lemieux on February 15, 2002 1:53 PM

I'm working on a spreadsheet which must calculate a person's age in years and months at a future date (e.g. 30 June 2002). If born on or before the 15th of the month we count a whole month. If born after the 15th we don't count the month.
e.g.
birthdate = 5 April 1942 then age at 30 June 2002 = 60yrs,3mons
birthdate = 16 Dec 1942 then age at 30 June 2002 = 59yrs,6mons
birthdate = 5 June 1942 then age at 30 June 2002 =
60yrs,1mon
birthdate = 16 June 1942 then age at 30 June 2002 =
60yrs
birthdate = 30 June 1942 then age at 30 June 2002 =
60yrs

Thanks in advance for all your help! All these conditions are driving me crazy.

Posted by Aladin Akyurek on February 15, 2002 2:11 PM

Assuming that the first birthday entry is in A1 (a date formatted entry) and the future date of interest (e.g., 30-Jun-2002) in E1,

in B1 enter: =DATEDIF(A1,$E$1,"y")&" years, "&DATEDIF(A1,$E$1,"ym")+(DAY(A1)&LT;15)&" months"

Drag down this as far as needed.

============

Posted by Susan Lemieux on February 16, 2002 2:03 AM

Thank you very much!