from Excel Time Import and Use in Arithmetically in Matlab

Hi,

I have a problem about time values in Matlab. I prepared excel sheet which contains times. I set the cell format hh:mm:ss in Excel and write the times for example 10:10:00. In another cell, I write 00:08:00 after that I want to subtraction 00:08:00 from 10:10:00. Normally, I want to get 10:02:00. But, after make xlsread and datestr, it makes the matrix in char, but I dont want it. I want the matrix m*n like in Excel. but, after datestr all times is written in 1 column in char type in matlab. I want to make my calculations like this for example, after xlsread or another method, t1=a(1,1) then t2=a(2,3) and then x=t2-t1 and the result as 10:10:00 - 00:08:00 = 10:02:00. Is it possible?

Thank you.

Answer 1

[Guillaume]

Save yourself a lot of trouble if you are using an mildly recent version of matlab (R2014b or later) and use readtable instead of xlsread which will import your excel file more easily, and convert your time into modern datetime instead of the outdated datestr.

datetime knows how to calculate time differences properly.

data = readtable('yourexcelfile');  %usually it's all that is needed
timediff = diff(data.yourtimecolumn);  %for example

[snr matlb]

Thanks, I tried. matrix occured as '0.41667'. Now, how can I go on with 10:00:00 and after it I want to make some arithmetics, <, > calculations etc.

[Guillaume]

Sorry, I don't understand at all what you are asking now.

[snr matlb]

No prob, I mean for example; I wrote 10:08:00 A1 cell and 00:08:00 in A2 cell in excel. After I apply your advise about usage of readtable the result in matlab like 0.41667 instead of 10:08:00 for example. But I cant continue my calculation with 0.41667, if I see the time value as 10:08:00 format in matrix, so I can check this for example: if 10:08:00 - 00:08:00 == 10:00:00 something... else something end. Finally I want to make a general algorithm for scheduling something. So, I need the time difference but in time format like hh:mm:ss.

[Peter Perkins]

The times are being read in as Excel date numbers. 0.41667 is (a rounded version of) 10/24.

Convert to durations:

>> x = 10/24
x =
         0.416666666666667
>> t = days(x); t.Format = 'hh:mm:ss'
t = 
  duration
   10:00:00

Because of the inherit round-off in Excel's representation of time, you are likely to get some round-off creeping in. You might consider rounding those durations to the nearest second or whatever.

It's also possible that your file also has dates, in which case you probably want to combine date+time and use datetimes, not durations.

[snr matlb]

thanks. now going on with numbers instead of time.