MATLAB Answers

readtable is not reading the time 12:00:00 AM?

20 views (last 30 days)
Abhinav on 17 Feb 2020
Edited: Yutaka Yamada on 24 May 2021
I am using 'readtable' to read an excel file. The first column of the file contains date and time in 'mm/dd/yyyy HH:MM:SS PM' format. The probelm is the following:
The readtable skips the time if the entry is '5/7/2018 12:00:00 AM'. I want to read entire date-time string. is it possibel to do it using the readtable?
I am using the following code to invoke the readtable:
Note: the readtable is able to read other time-entries perfectly; the above time-entry is 1427th row in the file, including header.
Star Strider
Star Strider on 18 Feb 2020
Noted. Apparently the MATLAB application was adding the AM/PM designations, since they do not appear in the Excel file, at least that I can see.

Sign in to comment.

Answers (3)

Walter Roberson
Walter Roberson on 17 Feb 2020
Your input format specification should be 'MM/dd/yyyy hh:mm:ss a' to read that.
m is minutes not month
H is only for 24 hour clock
M is for month not minutes
S is for fractions of a second
PM is the wrong specifier for AM/PM
Abhinav on 17 Feb 2020
I have attached the sample file now, along with the code lines.

Sign in to comment.

Motasem Mustafa
Motasem Mustafa on 19 Oct 2020
I have the same issue.
I have a data from a datalogger and when I am using a code to separate the year,month,day, and time from the data, it gives me an error. When I skip the row of the time 00:00:00, the code works.
When openeing the workspapce it shows that 00:00:00 is empty as shown in the screenshot below.
I am still struggling with the code and did not solve it.

Yutaka Yamada
Yutaka Yamada on 24 May 2021
Edited: Yutaka Yamada on 24 May 2021
Recentely I've tried similar thing.
Please try below code.
opts = detectImportOptions('readTime.xlsx');
opts.VariableTypes = 'datetime';
opts.VariableOptions.DatetimeFormat = 'mm/dd/yyyy HH:mm:ss a';
T = readtable('readTime.xlsx', opts);
I've attached the Excel file that I've used for the test.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!