Date formatting for sorting - followup

  • Thread starter Thread starter - Butch
  • Start date Start date
B

- Butch

I tried formatting with a created "Custom" format of "yyyy-mm-dd" and
that works EXCEPT, it doesn't affect dates before 1900. Why wouldn't it,
and how can I get it to affect all dates?
 
Excel doesn't like dates before 1900.

So those entries that look like dates (but are before 1900) aren't really dates.

You may be able to use:

=if(isnumber(a1),text(a1,"yyyymmdd"),someotherexpression)

But that other expression would depend on how those text entries looked.

Some combination of =mid(), =right() and/or =left() may do it for you.

12/25/1850
could be
=IF(ISNUMBER(A1),TEXT(A1,"yyyymmdd"),MID(A1,7,4)&LEFT(A1,2)&MID(A1,4,2))
 
Back
Top