Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort in reverse order | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) |