Forum Discussion
sorting date column oldest to newest
When I opened the file on my device I didn't find any problem in the dates!
Because these dates are compatible with my local date format which is: M/d/yyyy.
FYI:
Dates in the Excel file must be compatible with the date format in the operating system, if the original format of the dates is different from the local date format, problems with dates will appear, they may be treated as text, or they may remain formatted as dates, but not as you think it.
Examples:
Local Date is: (M/d/yyyy)
Excel Date is: 15/12/2017
This date will be treated as text because there is no month 15!
Local Date is: (M/d/yyyy)
Excel Date is: 1/12/2017
This date will still formatted as Date in Excel, but not the date you may want.
You may think it 1/Dec/2017, but it actually 12/Jan/2017 as the local date format!
Please take this information into account.
You have to make the dates in your Excel compatible with your local date format, so please provide me with your local date format is it (M/d/yyyy), (d/M/yyyy), or something else, to provide you with the appropriate solution!
OMG! this makes so much sense! thank you.
I tried changing my date local and all I could find was
in The Advanced options, to tick the "Use 1904 date system" - that didn't help.
My local date is: dd/mm/yyyy
1904 date system option isn't related!
Go back to advanced options and uncheck its checkbox!
Local date settings are found in Control Panel >> Region and Language.
But I don't recommend you to change them!
To solve the problem follow these steps:
Step 1
Sort the column that contains the dates from oldest to newest in order to move the text dates to the bottom.
Step 2
Highlight the text dates as shown in the below screenshot, and keep them highlighted.
Step 3
Go to Data >> Data Tools >> Text to Columns.
Step 4
In Text to Columns window click Next twice.
Step 5
Select Date from the Column data format group box, and select the date format of text dates that you have previously highlighted., If its format is US format (MDY), select it and hit Finish as shown in the below screenshot:
- This really worked for me. Thanks for taking the effort to share.
Thanks Haytham!
This is what I got, close but not 100% - I still need to clean it up...any suggestions how to take it from here?
I really appreciate your help - thanks again!
This little option is the cause of this problem, I forgot to tell you about it:
You should have cleared this check box!If you can undo the process, undo it, and take into account to clear this option.
If you can't, follow this workaround:
Next to the first cell contains 2017 use this function as shown in the below screenshot:
=DATE(YEAR,MONTH,DAY)
Then copy it down using the https://support.office.com/en-us/article/Fill-a-formula-down-into-adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6 to get the same result in the above screenshot.
After that, highlight the range, https://support.office.com/en-us/article/Paste-values-not-formulas-12687B4D-C79F-4137-B0CC-947C229C55B9
Then delete the previously separated values on multiple columns, and move the dates to the right place.
Hope that makes sense.
