Aug 11 2021 02:42 PM
Aug 11 2021 02:42 PM
I have a spreadsheet that I am working on and the dates that have been given on the sheet are in the European format (DD/MM/YYYY HH:MM), but I need these converted to the US format (MM/DD/YYYY HH:MM). I have tried many different possible solutions, but have come up stumped after each one. Even converting the EUR format to a serial doesn't give the proper date when basing a cell format to dd/mm/yyyy hh:mm and then converting it to a date column. Anyone know of an easy (and possibly overlooked) solution to this issue? The data I have is converted from a .csv file, but during the import the offending columns are left as general and not converted to DMY since the time is also important. I would even take splitting the date from the time in different columns at this point, but I can't seem to come up with a solution to do this either because of the offending DD/MM/YYYY formatting.
Aug 11 2021 09:22 PM
@mindlessmama How exactly were these dates imported? You mentioned that the CSV file contains dates in the European style. Then it's important that you indicate upon import that these are European dates. In Text-to-columns (TTC), for instance, you must indicate in Step 3 of 3 that these are dates "DMY". Now Excel will transform the dates to your system locale and you can choose whatever formatting you want and the time portions are preserved.
I repeated the TTC steps described above and could transform the list of dates to what you find in the attached workbook.
Aug 12 2021 12:20 AM
Here is a manual setting with cell formatting.
It works for me (Excel 2016), attached the file with the desired settings.
Format Code in Custom:
Wish all a nice day / night with lots of health, joy and love.
I know I don't know anything (Socrates)
Aug 12 2021 09:43 AM
@Riny_van_EekelenSo i retried this and during the import listed all of the DD/MM/YYYY HH:MM as DMY, but it still isn't recognized as such upon the import. I tried to reformat the fields after the import as dd/mm/yyyy hh:mm under custom, but it only adds leading zeros. It still will not show me the date as MM/DD/YYYY HH:MM as I need it to. Any ideas on how to break the date and time into separate cells? I'd gladly change the date after it is separated.
Aug 12 2021 09:45 AM
Aug 12 2021 12:11 PM