Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sandage_2000
 
Posts: n/a
Default Need to sort dates before 1900 in proper order

I need to sort dates before 1900 in proper order -- ones after 1900 are all
fine but any dates like 03/29/1865 will always sort by month and day but
NEVER the year except in the month area. Don't know if I am making sense --
but will be like:
03/29/1865
03/29/1866
03/29/1873
04/01/1863
04/01/1868
etc.
Thanks,
Judy M.
  #2   Report Post  
Dave O
 
Posts: n/a
Default

I can provide a chunky workaround, until someone provides a better
answer:

Assuming your 5 sample dates are in cells A1 ~ A5, and that single
digit months and days are always expressed with a leading zero:

In cell B1, enter this formula:
=VALUE(MID(A1,1,2))

In C1, enter this formula:
=VALUE(MID(A1,4,2))

In D1, enter this formula:
=VALUE(RIGHT(A1,4))

Copy those formulas to the remaining rows.

These formulas parse the original text string and convert them to
numbers. You can then highlight the rows and perform a multiple sort
on columns D, then B, then C. Chunky, but it works.

  #3   Report Post  
sandage_2000
 
Posts: n/a
Default

Thanks Dave but how do I then take these dates from 3 columns and put back
into one column in the mm/dd/yyyy format?

BTW - this DID work and it was also answer to another problem I had been
having because sometimes I have to merge different spread sheets and some
were typed in 3 columns (mo, day, year) and others in one column in
mm/dd/yyyy format -- so now I can use your answer here for converting that
too for the sorts. Double thanks and hopefully you or someone can tell me
how to get back to the single column date format.

Judy M.

"Dave O" wrote:

I can provide a chunky workaround, until someone provides a better
answer:

Assuming your 5 sample dates are in cells A1 ~ A5, and that single
digit months and days are always expressed with a leading zero:

In cell B1, enter this formula:
=VALUE(MID(A1,1,2))

In C1, enter this formula:
=VALUE(MID(A1,4,2))

In D1, enter this formula:
=VALUE(RIGHT(A1,4))

Copy those formulas to the remaining rows.

These formulas parse the original text string and convert them to
numbers. You can then highlight the rows and perform a multiple sort
on columns D, then B, then C. Chunky, but it works.


  #4   Report Post  
sandage_2000
 
Posts: n/a
Default

Sorry - my writing and mind don't seem to be going together today. Let me
start the second question again -- Now that I have this particular
spreadsheet sorted and also still in the mm/dd/yyyy format -- how do I put
other spreadsheets that are in the 3-column (mo, day, year) format into one
column in the mm/dd/yyyy format? Is there a way to merge these three so it
knows it is a date and shows the date format of mm/dd/yyyy? Hopefully I am
making more sense now -- I realized since the original one I did your value
formula on never changed the original column entries that when I wrote the
question it sort-of-sounded like it HAD changed and wanted you to know that
wasn't the case -- just needing the second answer for another spreadsheet
that is in the 3-column date format and need it combined into the single
column format so it is consistent when it is merged in a webpage I use.
Thanks.

"sandage_2000" wrote:

BTW - this DID work and it was also answer to another problem I had been
having because sometimes I have to merge different spread sheets and some
were typed in 3 columns (mo, day, year) and others in one column in
mm/dd/yyyy format -- so now I can use your answer here for converting that
too for the sorts. Double thanks and hopefully you or someone can tell me
how to get back to the single column date format.

Judy M.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort in reverse order sugarraisin Excel Discussion (Misc queries) 1 January 5th 05 05:23 AM
"-" ignored in sort Mike H Excel Discussion (Misc queries) 8 January 2nd 05 08:48 AM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 11:44 PM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 2 November 27th 04 02:55 AM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 0 November 26th 04 04:19 PM


All times are GMT +1. The time now is 06:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"