Convert Excel String time to number

My excel spreadsheet that I am reading into matlab with xlsread has text for the time in the format of 11:23:23 AM for example. this is a string with no date associated with it so when I try and run datenum or datevec I get an error Failed to lookup month of year. I also tried str2double but that just gives me a series of NaN . What am I looking to do? I need to convert a series of times in an array and then compute the elapsed time among other things.

Answer 1

[Ken Atwell]

This is not pretty, but you can split the string on the ':' and compute the number of seconds since midnight. The code below used cellfun to vectorize the code; a for loop could be use and might be easier to understand (but likely sacrificing performance).

 testData = repmat({'11:23:23'}, [10,1]);
 hms = cellfun(@(x) str2double(strsplit(x, ':')), testData, 'UniformOutput', 0);
 cellfun(@(x) x(1)*60*60+x(2)*60+x(3), hms)

You can compute duration so long as events don't cross midnight. If events do cross midnight, things become more complicate. In that case, if you have R2014b, the new datetime/duration functions may be useful. Here is a intro video.

[matlabuser12]

I only have 2012, but luckily they run from 7am until 5pm so no worries there. the time however is not recorded in 24hr format but 12hr. I will give this a try, thanks!

Answer 2

[Stalin Samuel]

HAVE U SEEN THIS http://www.mathworks.in/help/matlab/ref/str2num.html

[matlabuser12]

it doesnt work, also returns just NaN