| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel not recognizing dates as dates | Excel Discussion (Misc queries) | |||
| how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| How do I get the dates on an excel chart to stay as dates instead. | Charts and Charting in Excel | |||
| Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel | |||
Linear Mode
