convert excel datetime to time in matlab

in excel file C cell is datetime (2018-09-10 11:24:12 AM)

in matlab i only want to express 11:24:12

so i used a=datestr(xlsread(fileName,'C3:C30000'),'HH:MM:SS' ); but wrong time is shown 2018-09-10 11:50:27 AM -> 12:00:00 (like this)

[Walter Roberson]

Please attach a sample file for us to test with.

Answer 1

[dpb]

Specific file always helps as Walter suggests, but try

 a=datetime(xlsread(fileName,'C3:C30000'),'convertfrom','excel');
 a.Format='HH.mm.ss';

xlsread imports Excel times as Excel time, not Matlab datenum -consistent so must convert to get into correct form for other Matlab functions such as datestr. But, datenum has been superceded by the datetime class so recommend using it instead.

[Walter Roberson]

There are multiple representations for times in Excel, with datetime() being appropriate for some of them but not for others.

[dpb]

AFAIK, if they're actual internal dates inside Excel they're either base 1900 or 1904 and will be read as dates by xlsread that way. If they're actually text instead, then xlsread doesn't convert but readtable will if can.

I don't recall ever seeing any other behavior--what do you think is missing here, Walter?

Answer 2

[Peter Perkins]

If possible, use readtable, not xlsread. It handles many of the vagueries of Excel timestamps, and in recent versionw will automatically convert Excel serial dates (formatted as dates) to datetimes.

At that point, once you have datetimes, you can through away the date portion using the timeofday function. You can make the date portion "disappear" by changing the format (as in dpb's suggestion, but that can be a bit dangerous -- for one thing, two timestamps that look identical may not be.

[dpb]

But timeofday is a duration, not a time and there can be "issues" in use of it, too, we've spoken of before.