Excel formatting - can I change entry format?

[pooley]

I have been asked to tidy up a couple of our tracking sheets and include a formula that compares a childs chronological age at a date of test to their reading/spelling age.

 

Should be simple enough using the =dateif function but the staff have entered the spelling/reading age in the format 08:10 (8years 10 months) and I cannot get excel to recognize that format as a date format and nor can I find a way to change it without manually typing over it.

 

Any suggestions on how to change 08:10 to 8y 10m or even convert it to total months (106)

 

Thanks

 

Si

[andy_b]

A possible way:

 

If the "08:10" is text: =VALUE(LEFT(A1,FIND(":",A1)-1))*12+VALUE(RIGHT(A1,FIND(":",A1)-1)) will give you the months

 

If the "08:10" is formatted as time you will need an extra column to convert to text: =TEXT(A1,"hh:mm"), then apply the above to that.