Sorting on years and months

[Guest]

This is similar to the adding weight of fish question
Children's reading ages given in years and months - 3.10 = 3yrs 10 months, 3.01=3years 1 month - it all goes pea
shaped when you try to sort
TIA

 

[Guest]

Chris
I assume that the problem comes from using, for example, 3.8 for 3years 8 months in which case 3 years 8 months is greater than 3 years 10 months (3.8 = 3.80 > 3.10). In this case consider adding a column with the following formula (the age is supposed to be in cells A1)

=IF(100*(A1-INT(A1))<12,100*(A1-INT(A1)),10*(A1-INT(A1)))+100*INT(A1

The result shown in the right column below can be used to sort correctly your data. Alternatively using the same formula divided by 100 you can set-up a new age naming convention (where 3 years 8 months becomes 3.08 rather than 3.8). This new naming convention would sort correctly.

3.01 30
3.2 30
3.3 30
3.4 30
3.5 30
3.6 30
3.7 30
3.8 30
3.9 30
3.10 31
3.11 31

Regard
Jacques Bru

----- Chris Meech wrote: ----

This is similar to the adding weight of fish question
Children's reading ages given in years and months - 3.10 = 3yrs 10 months, 3.01=3years 1 month - it all goes pea
shaped when you try to sort
TIA

 

[David McRitchie]

Hi Chris,
Can you be more specific as to what you have and what you want.
Dates and times are stored as days past a certain date for the
1900 date system in Excel that would be days past Dec 31, 1899 (kind of).
3.01 is not 3 years on month it would be 3 days and the .01 days would be
..24 hours or 14.4 minutes.

Generally you would calculate an age based on birthdath and
a reference date..

Suggest reading the following:
http://www.cpearson.com/excel/datedif.htm
http://www.cpearson.com/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm