xlsread date reading not working

I have an excel file where column C has date data in the following format:

mm/dd/yyyy hh:mm:ss

for example:

6/1/2017 0:01:49

6/1/2017 17:51:45

6/2/2017 5:55:13

etc..

When I use xlsread:

 [a b c] = xlsread('filename.xlsx','C2:C1000');

I get the necessary data in a and c, with nothing in b. But when I try to convert the data in a to the format that I need with datestr, it doesn't seem to be working. If I try

 datestr(a,'mm/dd/yyyy hh:mm:ss')

I get an error saying "Unrecognized month format". Any thoughts?

[Walter Roberson]

You used mm for both month and minutes.

Excel dates are stored in numeric form, so they probably would not show up in the text output of xlsread()

Answer 1

[Micheal Simpson]

So I found that the correct code is:

 e = datetime(a,'ConvertFrom','excel')

However, the times are off by 20-40 seconds each. How to get the seconds correct?

[Peter Perkins]

Micheal, if possible you should use readtable instead of xlsread, especially in recent versions of MATLAB.

If your "a" variable is indeed Excel serial date numbers, then your observation that the result is 20-40 seconds off makes me suspicious that the numbers are not what you think they are. Can you provide an example? I think you're going to need to show what it looks like in Excel, how it is stored in Excel, what number comes in to MATLAB, and what the datetime that's created is.

Answer 2

[shivani badodiya]

I have the problem with xlsread ,I used this [~, ~, raw] = xlsread('file_name','sheet_name'); please suggest me what is the problem I got time column in this format

'Time'

0

0.500000000000000

0

0.500000000000000

[Walter Roberson]

We do not know that it is wrong...

Can you attach a sample of the file ?

[shivani badodiya]

I attached the file Which format of time i got in matlab.so what is the problem

[Walter Roberson]

Times of 0 and 0.5 appear to be correct for that file for the second time field. You have two fields labeled "time", the first of which is in format such as 12:00:00 but the second one is in the format 0 or 0.5

It appears to me that the second time column is a number in "fractions of a day". If you were to convert the second date column to serial date number and add the 0 or 0.5 day, then you would get the serial date number equivalent to the date and time shown in the first two columns. There does not appear to be an error.