Convert Excel String time to number

2 views (last 30 days)
matlabuser12 on 30 Oct 2014
Commented: matlabuser12 on 30 Oct 2014
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.

Answers (2)

Ken Atwell
Ken Atwell on 30 Oct 2014
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.
  1 Comment
matlabuser12 on 30 Oct 2014
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!

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!