date format

[Dal]

Hi all,

I have a spreadsheet containing lots of pivot tables. The source data
contains date formulas in the following ways:

=IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11

=IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07

As a result, when I try to sort my pivot table data by date, the dates that
are shown as Nov-07 are recognised as text therefore only allowing me to sort
by alphabetical order. The dates that are like 2007-11, i am able to sort it
by the earliest/latest dates.

Is there a way i can get excel to recognise all formats so that i am able to
sort by how old the dates are?

 

[Bob Phillips]

Use =IF(FR5="","",FR5)

and format the cells as required.