several date formats used in the same column. Unable to unify them.

Hey experts!

I am currently maintaining a data base of which several columns represent a date and time. The report currently has 200000+ lines involved. The data is pulled from a report in CSV format.

It turns out that the source report creator changed their date format somewhere in the past. The changed format is visible in the formula bars and shows the follow distinct format differences:

Format 1: 'M/D/YYYY H:MM' (Example: 4/19/2020 21:18)
Format 2: 'M/D/YYYY H:MM:SS' (Example: 04/06/2020 06:49:00)

The result is that the end product (report) will not correctly reflect all the values within the source data due to the date format difference.
As such I am looking for a way to unify the formats, which i am currently having some issues with. i have tried the following to unify the data set:

1.) Changing the date formats for the entire columns via CTRL+1 and selecting a date format.
2.) Trying to change the data from text to date by using text to columns.

The results of this is that 'Format 1' will stay in the same format, regardless of what i do to change the date formats, while 'Format 2' will change without issue.

I have attached a sample excel file. The apparently unmovable data can be found in the first 4 lines. Would any of you be willing to guide me to what i am missing?

Regards,

Neogenic - an Excel padawan

Welcome to the forum.

Are you using Office 365? If so, please update your forum profile.

For whatever reason the top half of your dates are not saved in excel as date codes. They are text. It needs the background date code in order to change the formatting.

Insert a helper column next to the affected columns. (we can delete the column after.)

Lets say column A is a date. In column B have the following formula refer to column A.

=DATEVALUE(TEXT(A2,"MM/DD/YYY"))+TIMEVALUE(TEXT(A2,"HH:MM:SS"))

You could then copy and paste the values over the original column and format in whichever format you'd like.

Attached in the resolved example of your spreadsheet.

Do we know why part of the data refuses to be reformatted?

As has already been noted, the reason is that part of the data is still text (and, I expect, the other part has been incorrectly imported). The solution is to convince Excel to import the data correctly. Without knowing exactly what you did with the text import wizard, it is difficult to say why your latest attempts have failed. One way or another the solution is to import the text file in a way that allows Excel to correctly read the text and convert the date text to real date/time serial numbers. Exactly what did you try in your latest attempts with the text import wizard? I would have expected:

1) Text import wizard step 1 -- delimited.
2) text import wizard step 2 -- choose the appropriate delimiter (comma??)
3) text import wizard step 3 -- for the columns containing date/time text, choose the Date option and specify MDY in the dropdown.
4) double check the text import to see if everything imported correctly.

Hi MrShorty,

Please see attached a file which shows screenshots of the actions which i took, as well as a new example file of the results.

Excel seems to persists in viewing some of the imported data, specifically the once which in seem to be marked with 2 digits after each slash,
as text rather than date values.

I am trying to find out why this is happening, because i am hoping to correct this en mass.

Regards,

Neogenic

Try in D2:

Please Login or Register  to view this content.

Darg down and to column E

it change day to month and vice versa.

@neogenic
Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

The reason you are getting text dates is because the number between the two slashes (which represents days in US format but which your system will interpret as months) is greater than 12 - Excel can't make sense of that, and so it returns it as text.

In your example file you can change all the "dates" to the correct format by using this formula (e.g. in D2):

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2))+A2-INT(A2),DATE(MID(A2,FIND("/",A2,3)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))+RIGHT(A2,LEN(A2)-FIND(" ",A2)))

Basically, if the cell is a number (i.e. it has been interpreted as a date) then reverse the day and month, otherwise extract the appropriate values from the text and convert to a date. Apply a custom format of dd/mm/yyyy hh:mm:ss to the cell (and widen it to see them all), then copy down to D30. You can also copy across into column E to change the dates in column B. As I said before, this will take some time to calculate if you have 200,000+ rows (and 12 columns), but once you have done that you can fix the values and delete the original columns, and then save it as an .XLSX file so you won't have to do it again.

Hope this helps.

Pete

EDIT: I suppose I should have refreshed the screen before posting !!

I notice that bebo021999 and Pete_UK's solutions are focused on fixing the issue after import. If you are happy with that, then you can ignore this. Going through your screenshots, it appears that you are specifying DMY dates at the 3rd step of the import wizard. I have seen others who have mistakenly believed that this step is telling Excel what you want the dates to look like after import, but that is not what you are specifying here (you will use number formatting after import to specify what you want the dates to look like). At this step of the text import wizard, you are telling Excel what to look for in the text file. By specifying DMY at this step, Excel will take the first date, for example, and interpret is as 4th day of the 20th month of the year 2020, which, of course, makes no sense, so it leaves it as text.

I expect that, if you will specify MDY dates at this step of the wizard, then Excel will be able to interpret these dates correctly at import (remember that you can use number formatting after the import to display them however you want) and you won't need formulas to fix the dates after import.

If you're happy with the post import formulas to fix the import, then you can ignore this. If you are interested in fixing this at import rather than after import, you can try specifying MDY dates while importing.

-Can't post links yet-

Hi MrShorty,

Thanks for the valuable tip. I certainly fell into the same pit trap when it comes to using the import wizard. Unfortunately the file is simply too large and Excel runs out of resources when trying to import the data. Unfortunately that means that the only way to reliable unify the data is through a formula. I do appreciate the tip though.
Now to convert this sheet correctly into Tableau, which is another world of issue on it's own.

Neogenic

Why are you trying to post a link???

You can't until you have at least 10 posts.

You will be able to use it once you have made 10 posts (but it won't produce a live link, as we don't have that facility).