Age formulas in MS Excel

August 25, 2007 7:42 PM

I need to put some test score data into Excel that works with children's ages in years and months.

I have been working with some children to collect some research data. In order to work with my numbers I need to know how to be able to get Excel to see that 7.11 is actually seven years and eleven months.

How can I do this?

posted by chairish to computers & internet (5 comments total)

Say the years.months data is in column A. In B1, enter this formula:

=+INT(A1)*12+(A1 - INT(A1))*100

This will give you the total number of months represented by the datum in A1, which for 7.11 is of course 95. I assume that will work for you?
posted by cerebus19 at 7:58 PM on August 25, 2007

(That first plus sign isn't necessary, btw.)
posted by cerebus19 at 8:00 PM on August 25, 2007

chairish, if your data doesn't contain leading zeros after the period then cerebus19's formula could return incorrect results. For example, it will treat 7.4 as seven years and forty months and both 7.1 and 7.10 as seven years and ten months.

If your data does omit leading zeros (i.e. five years and two months appears as 5.2, not 5.02), I'd recommend the following function:

=LEFT(A5,FIND(".", A5)-1)*12+MID(A5,FIND(".", A5)+1,2)

If your age data omits leading zeros and an age of five years might appear as 5, not 5.0, I'd recommend the following function:

=IF(ISERROR(FIND(".",A1)), A1*12, LEFT(A1,FIND(".", A1)-1)*12+MID(A1,FIND(".", A1)+1,2))

This last function returns the following values:

Age String   Age in Months
5            60
5.1          61
5.01         61
5.2          62
5.3          63
5.10         70
5.11         71
posted by RichardP at 9:43 PM on August 25, 2007

By the way, if your age data does omit leading zeros, make sure you're careful when importing or manually entering your ages that you use text format, not number format, otherwise Excel will automatically convert values with trailing zeros (e.g. 5.10 into 5.1). If you are importing make sure your choose the Text format for the Age column in the Text Import Wizard. If you're manually entering ages, enter ages with trailing zeros such as 5.10 by typing '5.10 (i.e. with a leading single quote). You might consider avoiding the issue by using a delimiter other than "." or any of date/time delimiters, perhaps "y" or "|" (i.e. use 5y10 or 5|10).
posted by RichardP at 10:31 PM on August 25, 2007

Thanks for these responses. You've made smoother work of my Monday :)
posted by chairish at 12:03 AM on August 26, 2007