Date sort problems

[Guest]

I have a data base in Excel on A Mac with one column listing dates.
I need to sort the data into date order but some of the date entries appear
not to be treated as dates.
The format tab indicates that all the entries ARE dates, but if I do a
DATEVALUE() on the range, some convert to numbers but others to "VALUE".
Even if I re-enter the dates manually, they still do not sort porperly.
Anyone able to help please?

 

[Richard Neville]

When I switched from Mac to PC some years ago, I had trouble with dates
created on a Mac because it uses the 1904 date system, while PCs use the
1900 system and some dates were four years off. Check the setting in
Tools-Options-Calculation.

 

[Bernie Deitrick]

Graham,

Insert and format a column as date, then use a formula in that column to
convert your values. Assuming your first date in in cell A1:

=IF(ISERROR(DATEVALUE(A1)),A1,DATEVALUE(A1))

Copy down to match your data, then sort based on the formula column.

You could copy and pastespecial values and delete your original data to
simplify things.

HTH,
Bernie
MS Excel MVP