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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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
52,156
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,879
Messages
5,410,934
Members
403,335
Latest member
ddaveryos

This Week's Hot Topics

Top