Hi



In attached file is an example how I'd do this (if I understood correctly what you did want to do). I added an optional column where birth date (NB! Not Birthday!) is calculated too.

User has to follow a simple rule (all years less than 1930 must be entered with full year number). When it is too difficult for user(s), then it will be best to make full year number mandatory (i.e. set up data validation rule which restricts entering year numbers less than 1900). With all other solutions you'll risk to err with century for year range 0-29.

Attached Files

Age.xlsx (11.6 KB, 8 views)

Marcia, I haven't looked at Arvi's solution yet, but I think I must have misunderstood your original post. I thought you had a worksheet with a birth date, and you were trying to parse out the year, month and day in order to calculate the victim's age. Now that I read again, I think you're saying your worksheet has the birth date in separate month, day and year columns, and you're trying to reassemble them into a date so you can calculate the age. Is that right?

If so, assuming month is in A2, day in B2 and year in C2, I would try this: The birth date is =DATE(C2,A2,B2). The age in days is =TODAY()-DATE(C2,A2,B2), and if you want it in years (as of course you do) it can be =(TODAY()-DATE(C2,A2,B2))/365.

That's assuming a full four-year birth year. If you have only two years, then you have to make some decision about the year—that is, if the victim is born in "19" is that 2019 or 1919? You'd want to include something like =IF(C2<20,2000+C2,1900+C2).

And if I'm ~still~ not reading carefully, I apologize; try to explain again.

Thanks Bob and Arvi. I'm sorry for not attaching a sample sheet on which the readers could try and find a solution to my request. The IF formula required a helper column but it's okay. BTW I added IF birth year is 2000, 2000.
I will try the validation list and file it for future reference. I filtered those years below 1930 and retyped the 4 digit years.