Excel date & Time in 24 H instead of AM/PM

0

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'.

John Heinonen

Posted 2019-06-19T13:44:40.243

Reputation: 1

When importing, you need to also tell Excel the format of the column in the CSV file with regard to is it MDY or DMY – Ron Rosenfeld – 2019-06-19T18:09:45.437

Welcome to Super User! It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See How can one link/merge/combine/associate two accounts/users? and/or I accidentally created two accounts; how do I merge them? for guidance on how to merge your accounts.

– DavidPostill – 2019-06-20T17:09:20.980

Answers

0

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.

Ron Rosenfeld

Posted 2019-06-19T13:44:40.243

Reputation: 3 333

Asked: 2019-06-19T13:44:40.243

Viewed: 346 times

Active: 2019-06-20T08:51:07.337