Sorting and Colour Reading Age cells

[cosmica67]

Hi,

I have a problem that I hope someone can help solve. I have included test data which shows reading ages in the form of YY/MM (for example 0/5 represents 0 years and 5 months).

I need to somehow order this column from largest to smallest or vice versa and also colour cells automatically if the cells show a reading age less than 1 year 0 months (1/0).

I have included the data on a spreadsheet.

Many thanks

[Sadath31]

Hi,
select D8:d67 and enter the CSE formula
=SUBSTITUTE(TEXT(SMALL(SUBSTITUTE(B8:B67,"/",".")*1,ROW()-7),"0.##"),".","/")

[Sadath31]

Hi
please consider this (a small change)
=SUBSTITUTE(TEXT(SMALL(SUBSTITUTE(B8:B67,"/",".")*1,ROW()-7),"0.0#"),".","/")

[cosmica67]

HI,

Many thanks for your prompt reply.

Do I need to highlight the cells first or past the formula into the first cell and drag/copy down?

Regards

[cosmica67]

HI,

Managed to do it! Many thanks.

Regards

[cosmica67]

Does this formula colour the cells as well? for example, if I want to highlight cells with reading ages below 1 year months they colour in yellow or any chosen colour.

Regards

[Sadath31]

Hi
conditional formating formula for less than 1yr

conditional formating>New Rule>use formula to determine.........
enter the formula and select format colour

=SUBSTITUTE(B8,"/",".")*1<1

thanks