Convert Excel String time to number

2 次查看(过去 30 天)
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.

回答(2 个)

Ken Atwell
Ken Atwell 2014-10-30
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 个评论
matlabuser12
matlabuser12 2014-10-30
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!

请先登录,再进行评论。


Stalin Samuel
Stalin Samuel 2014-10-30

类别

Help CenterFile Exchange 中查找有关 Calendar 的更多信息

Community Treasure Hunt

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

Start Hunting!

Translated by