1

I have tried many different tips and tricks to have the below date converted to 24H format in Excel 2016 with no luck. It seems to not be identified as date & time at all. Some things I tried:

  1. When importing from CSV, marking column as date and time
  2. Importing as is (from Aternity web page) and marking as date, time custom (mm-dd-yyyy TT:MM:SS)
  3. Using Transpose (import has times in rows and not in columns) and splitting date from time, then marking them as such.

Nothing seems to work. Format: 6/13/2019 1:45:00 PM

It is clear to me the export from Aternity is flawed as all date and time up to 06-12-2019 23:55:00 has one format, and another from 6/13/2019 12:00:00 AM which makes my graphs and pivot 'messy'.

Improve this question
2

1 Answer 1

Reset to default
1

For a CSV file:

Data tab --> Get&Transform Data --> From Text/CSV

When the file opens, you are probably using Comma` as the delimiter. This will leave the Date/Time column together as a single column.

Transform Data

Right click on the date column and choose Change Type --> From Locale from the drop down menu.

Set the Data Type to Date/Time

Then choose the locale that matches the format in the CSV file (eg: English-Europe or English-United States.

enter image description here

Then OK and Close and Load and you should be converted properly.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .