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.
@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.
@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.
I have attempted this as well, but it still isn't reading the dates properly. It will read some, so long as the day displayed is less than 12. However with this the information is confused and European format 13/01/2019 is not recognized and 12/01/2019 is recognized as December 1, 2019 when converted to US format.
