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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting MM/DD/YYYY into just MM/DD and Sorting 1
Converting date format 4
Need help formatting a sheet 0
Form Control Calendar date will not change its format 1
Date format 2
convert date 1
Forcing a date entry format of dd/mm/yyyy 1
Excel is changing date format incorrectly 7

Back
Top