I have extracted 'time' from excel sheet but got in different format.

my excel time file is in format h:m:s.

0:00:00
0:15:00
0:30:00
0:45:00
1:00:00
1:15:00
1:30:00
1:45:00
2:00:00
2:15:00
2:30:00

I extracted using code:

data = xlsread('winddata','C10:C2980'); % for the month of Jan
Time=data(:,2);

But I got time as 0

0.0104166666666667
0.0208333333333333
0.0312500000000000
0.0416666666666667
0.0520833333333333
0.0625000000000000
0.0729166666666667
0.0833333333333333
0.0937500000000000
0.104166666666667

I do not understand what is this? How can I get time in correct format in matlab as well or hourly. How can we do increment on such excel file in matlab.

Answer 1

[per isakson]

You are right, it's confusing and it's because Excel stores its serial date number internally and shows it on a format chosen by the user. The internal value is shown when the user chose "No specific format". xlsread reads the internal value regardless of what is displayed.

The string 00:15:00 in the cell does not tell whether the underlying value is

• a character string, which is displayed with the format General or
• a serial date number, which is displayed with the format Time

Remains the question of how to convert Excel date and time to Matlab. I have long stopped using Excel and forgotten how to make this conversion.

Maybe the new function readtable fixes it automagically.

Guess that the numbers you see are in the unit "day" and make a test

>> 0.0104166666666667 * (24*60)
ans =
   15.0000

>> datestr( 0.0104166666666667, 'HH:MM:SS' )
ans =
00:15:00

Indeed they are. Multiply by 24 to get hours or by (24*60) to get minutes.

However, it becomes more tricky when it comes to dates.

This has been ask several times, e.g

• https://uk.mathworks.com/matlabcentral/answers/335796-excel-date-conversion-into-matlab#answer_263340
• https://uk.mathworks.com/matlabcentral/answers/25216-reading-time-dates-from-excel#answer_33117 has the magic number datestr(DateTime+datenum('30-Dec-1899')). ( 1900 was not a leap year.)

@Kian Azami points at datetime, which was "Introduced in R2014b". Thus, the short answer regarding dates is

>> t = datetime( 0.0104166666666667,'ConvertFrom','excel')
t = 
   1899-12-31 00:15:00

Answer 2

[Kian Azami]

Maybe you can change them to dates and separate the time as follow:

data = xlsread('winddata','C10:C2980'); % for the month of Jan
Time=data(:,2);
dates = char(datetime(Time,'ConvertFrom','datenum'))
YourTimes = dates(:,end-7:end)

Now you have strings which contain the time. You can change them also to Date type if it is necessary.