Tricky formatting and Date Formula

[Terrie]

I need to calculate the difference in ages for a school
tracking program. The problem is in the fact that the ages
are recorded as follows:
6.1 is a child 6 years and 1 month
6.2 is a child 6 years and 2 months etc
6.10 is a child 6 years and 10 months
6.11 is a child 6 years and 11 months

The formatting cannot differentiate between 6.1 and 6.10.
Is it possible to get around these problems so that I can
a) record the ages so that they mean something in the
spread sheet
B) create a formula that will work out the age differences
between a chronological age of, for example, 8.1 (8 years
and 1 month) and a reading age of 7.4 (the difference
being 9 months)
Would really appreciate any help
Terrie

 

[Joseph]

You could try formatting the cells containing 6.1, 6.10 etc as text.
That way Excel will pick them up as words rather than numbers.

Cheers

 

[Sandy Mann]

Terrie,

a)
Insert two additional columns then select the column of data which, as it
has 6.1 and 6.10 in it, I assume is formatted as text.

In the Worksheet Menu Bar select "Data" > "Text to Columns..."

In the dialog box that will appear make sure that "Delimited" is checked and
then select "Next"

In the "Other" box enter a decimal point and you will see the data in the
"Data Preview" separate.

Select "Next" and in make sure that "General" is selected in the "Column
Data Format" and in the "Destination" box enter the cell in the 1st newly
inserted column then click "Finish"

The data will be entered in the 2 new columns as numbers, 1st column years,
2nd column months.

b)
In the columns you want the differences, enter the formula: (assuming
"Years" in column B & "Months" in column C)

=(B3*12+C3)-(B2*12+C2)

It would however be much more meaningful if you were to enter the children's
dates of birth and use DATEDIF to work out the difference in ages.

Post back if you want help

HTH

Sandy