Excel dates
 

I have a spreadsheet with a column of dates between 18xx and 20xx. I cannot
sort this column without the 18xx dates not sorting with the rest of the
dates. Is there some way to cause 18xx dates to sort right

Excel dates
 

XL does not understand dates prior to Jan 1 1900. To that end all of your
dates in the 18xx are actually stored as text where as the dates in 19xx and
20xx are stored as dates. This will keep your dates from sorting correctly.

As a work around you can convert all of your dates to text and then the
sorting will be consistent. To make that work correctly you will want to
format the dates Year/Month/Day
--
HTH...

Jim Thomlinson


"david d" wrote:

I have a spreadsheet with a column of dates between 18xx and 20xx. I cannot
sort this column without the 18xx dates not sorting with the rest of the
dates. Is there some way to cause 18xx dates to sort right

Excel dates
 

What is the best way to format dates as yy/mm/dd?

"Jim Thomlinson" wrote:

XL does not understand dates prior to Jan 1 1900. To that end all of your
dates in the 18xx are actually stored as text where as the dates in 19xx and
20xx are stored as dates. This will keep your dates from sorting correctly.

As a work around you can convert all of your dates to text and then the
sorting will be consistent. To make that work correctly you will want to
format the dates Year/Month/Day
--
HTH...

Jim Thomlinson


"david d" wrote:

I have a spreadsheet with a column of dates between 18xx and 20xx. I cannot
sort this column without the 18xx dates not sorting with the rest of the
dates. Is there some way to cause 18xx dates to sort right

Excel dates
 

RightclickFormatCellsCustom Type:
yy/mm/dd

Using a worksheet function
=TEXT(A1,"yy/mm/dd")
(this will remain as a text string not a date..)

If this post helps click Yes
---------------
Jacob Skaria


"david d" wrote:

What is the best way to format dates as yy/mm/dd?

"Jim Thomlinson" wrote:

XL does not understand dates prior to Jan 1 1900. To that end all of your
dates in the 18xx are actually stored as text where as the dates in 19xx and
20xx are stored as dates. This will keep your dates from sorting correctly.

As a work around you can convert all of your dates to text and then the
sorting will be consistent. To make that work correctly you will want to
format the dates Year/Month/Day
--
HTH...

Jim Thomlinson


"david d" wrote:

I have a spreadsheet with a column of dates between 18xx and 20xx. I cannot
sort this column without the 18xx dates not sorting with the rest of the
dates. Is there some way to cause 18xx dates to sort right