Formatting Dates from outside software system

mjohnston0209

Board Regular
Joined
Nov 6, 2017
Messages
55
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Assuming the dates are in a consistent format:
Code:
=TEXT(LEFT(A1,FIND(" ",A1)),"m/d/yyyy")
...where A1 is your bad format date.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top