0
I am working on a project to clean up my company's data. One particularly horrid file is an Excel sheet with approximately 30,000 rows.
The date columns in this sheet are populated with dates in many different formats, many of which are not valid Excel date formats.
Here are some of the formats:
8/26/2017
20170925
2017-08-10
43026
43040.46597
I need to somehow get all of these to one, standard format without a timestamp.
Due to so many different formats, I've tried checking the LEN()
in order to group the formats together (though some differing formats have the same length too).
I have spent literal hours going through several methods to convert the dates and it works fine to get the correct format in a new column, but I can not copy/paste that corrected format back into the original column due to the filtering I need to use.
Selecting only the visible rows works fine, but when pasting, I still end up losing a lot of data in the hidden rows, no matter what I do.
Is there any way to fix this without having to go through all 30,000 rows one at a time and copy/pasting or manually typing the correct date format?
1How does 43026 become a date – Dave – 2018-04-09T05:06:19.243