Formatting Dates from outside software system

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
8
To anyone who can help,

Our software system exported data to excel. However, the date formats cannot be sorted properly. I noticed that if I double click on a cell, so my cursor is showing and then press escape, the format automatically changes to a date format that works for sorting. I have been trying to figure out how to do this as one massive change, but have been unsuccessful. Paste Special doesn't work. I have used left formulas into a column I inserted next to the data and even tried macros. For macros, I don't know how to input my cursor into a cell without the coding listing what the value actually is (there are thousands of unique values). Can anyone help?

Bad Format 8/22/2019 12:00:00 AM
Good Format 8/22/2019
 

william_man

New Member
Joined
Oct 31, 2019
Messages
19
Assuming the dates are in a consistent format:
Code:
=TEXT(LEFT(A1,FIND(" ",A1)),"m/d/yyyy")
...where A1 is your bad format date.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Code:
=TEXT(LEFT(A1,FIND(" ",A1)),"m/d/yyyy")
That does not lend itself to sorting dates in the correct format. The TEXT function will return a text value, not a date value.
And if you if choose that format, it won't sort the way you need dates to, (i.e. 1/1/2019 would come before 7/30/2016).
If you wanted to sort dates formatted as text, you would need to use a format that list years first, then months, then days, and accounts for leading zeroes, so a format like "yyyy/mm/dd").

However, no formulas should be necessary. I think they can convert all the values to valid date entries in a single step using "Text to Columns".
Simply:
1. Select the column
2. Go to the Data menu and select "Text to Columns"
3. Click next until you get to Step 3
4. Choose the Date option, making sure it is using the "MDY" format
5. Click finish

That should convert all those dates to a valid date entry.
You can then set any desired date formatting that you want on the cell.
 

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
8
Thank you both for your input! I wish I would have seen the text to columns answer earlier. I used the date function after I separated out the original date format using left, right, mid, and len formulas. Much harder.
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top