Read excel file with Matlab

Hello,

I have excel file with three columns "Time", "Altitude, And "Shadow length" that I want to read in Matlab. I tried this commands below, however, the column "Time" format change to decimal number. What can I do to keep the time format the same?

Thank you!

T = readtable('Shadedata.xlsx');
opts = detectImportOptions('Shadedata.xlsx');
preview('Shadedata.xlsx',opts)

Answer 1

[Star Strider]

One option is to use the datetime 'ConvertFrom' name-value pair. For the ‘value’ argument, both 'excel' and 'posixtime' work, with 'excel' appearing to be correct (in that it gives 15-minute intervals) —

% F = fileread('Shadedata.xlsx')
% C = readcell('Shadedata.xlsx')

T = readtable('Shadedata.xlsx', 'VAriableNamingRule','preserve')

T = 40×3 table
     Time      Altitude [degree celcius]    Shadow length [m]
    _______    _________________________    _________________

    0.32292               1.34                   2135.8      
    0.33333               2.34                   808.83      
    0.34375               3.34                   495.25      
    0.35417               4.34                   357.42      
    0.36458               5.34                   280.44      
      0.375               6.34                    231.5      
    0.38542               7.34                   197.77      
    0.39583               8.34                   173.22      
    0.40625              17.92                   154.65      
    0.41667              19.63                   140.22      
    0.42708              21.22                   128.79      
     0.4375              22.68                   119.62      
    0.44792              24.02                   112.22      
    0.45833               25.2                   106.24      
    0.46875              26.24                   101.45      
    0.47917              27.11                    97.68      

T1 = T;
T1.Time = datetime(T1.Time, 'ConvertFrom','excel')

T1 = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    31-Dec-1899 07:45:00               1.34                   2135.8      
    31-Dec-1899 08:00:00               2.34                   808.83      
    31-Dec-1899 08:15:00               3.34                   495.25      
    31-Dec-1899 08:30:00               4.34                   357.42      
    31-Dec-1899 08:45:00               5.34                   280.44      
    31-Dec-1899 09:00:00               6.34                    231.5      
    31-Dec-1899 09:15:00               7.34                   197.77      
    31-Dec-1899 09:30:00               8.34                   173.22      
    31-Dec-1899 09:45:00              17.92                   154.65      
    31-Dec-1899 10:00:00              19.63                   140.22      
    31-Dec-1899 10:15:00              21.22                   128.79      
    31-Dec-1899 10:30:00              22.68                   119.62      
    31-Dec-1899 10:45:00              24.02                   112.22      
    31-Dec-1899 11:00:00               25.2                   106.24      
    31-Dec-1899 11:15:00              26.24                   101.45      
    31-Dec-1899 11:30:00              27.11                    97.68      

T2 = T;
T2.Time = datetime(T2.Time, 'ConvertFrom','posixtime')

T2 = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    01-Jan-1970 00:00:00               1.34                   2135.8      
    01-Jan-1970 00:00:00               2.34                   808.83      
    01-Jan-1970 00:00:00               3.34                   495.25      
    01-Jan-1970 00:00:00               4.34                   357.42      
    01-Jan-1970 00:00:00               5.34                   280.44      
    01-Jan-1970 00:00:00               6.34                    231.5      
    01-Jan-1970 00:00:00               7.34                   197.77      
    01-Jan-1970 00:00:00               8.34                   173.22      
    01-Jan-1970 00:00:00              17.92                   154.65      
    01-Jan-1970 00:00:00              19.63                   140.22      
    01-Jan-1970 00:00:00              21.22                   128.79      
    01-Jan-1970 00:00:00              22.68                   119.62      
    01-Jan-1970 00:00:00              24.02                   112.22      
    01-Jan-1970 00:00:00               25.2                   106.24      
    01-Jan-1970 00:00:00              26.24                   101.45      
    01-Jan-1970 00:00:00              27.11                    97.68      

Choose the result that makes the best sense.

.

[Sanley Guerrier]

Looks great, Thank you!

my date is 10-Feb-2023, how do I update your code to have someting like 10-Feb-2023 07:45:00 ?

[Les Beckham]

One possible way to offset the data to that desired date is this:

T = readtable('Shadedata.xlsx', 'VariableNamingRule', 'preserve');
T1 = T;
offset = exceltime(datetime(2023, 2, 10));
T1.Time = T1.Time + offset;
T1.Time = datetime(T1.Time, 'ConvertFrom', 'excel')

T1 = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      

[Sanley Guerrier]

Perfect, thanks a lot!

[Star Strider]

As always, my pleasure!

I don’t see anything that directly addresses that problem ('PivotYear' would only change the year) and dateshift doesn’t appear to provide the necessary options.

So, I improvised —

T = readtable('Shadedata.xlsx', 'VAriableNamingRule','preserve')

T = 40×3 table
     Time      Altitude [degree celcius]    Shadow length [m]
    _______    _________________________    _________________

    0.32292               1.34                   2135.8      
    0.33333               2.34                   808.83      
    0.34375               3.34                   495.25      
    0.35417               4.34                   357.42      
    0.36458               5.34                   280.44      
      0.375               6.34                    231.5      
    0.38542               7.34                   197.77      
    0.39583               8.34                   173.22      
    0.40625              17.92                   154.65      
    0.41667              19.63                   140.22      
    0.42708              21.22                   128.79      
     0.4375              22.68                   119.62      
    0.44792              24.02                   112.22      
    0.45833               25.2                   106.24      
    0.46875              26.24                   101.45      
    0.47917              27.11                    97.68      

T.Time = datetime(T.Time, 'ConvertFrom','excel')

T = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    31-Dec-1899 07:45:00               1.34                   2135.8      
    31-Dec-1899 08:00:00               2.34                   808.83      
    31-Dec-1899 08:15:00               3.34                   495.25      
    31-Dec-1899 08:30:00               4.34                   357.42      
    31-Dec-1899 08:45:00               5.34                   280.44      
    31-Dec-1899 09:00:00               6.34                    231.5      
    31-Dec-1899 09:15:00               7.34                   197.77      
    31-Dec-1899 09:30:00               8.34                   173.22      
    31-Dec-1899 09:45:00              17.92                   154.65      
    31-Dec-1899 10:00:00              19.63                   140.22      
    31-Dec-1899 10:15:00              21.22                   128.79      
    31-Dec-1899 10:30:00              22.68                   119.62      
    31-Dec-1899 10:45:00              24.02                   112.22      
    31-Dec-1899 11:00:00               25.2                   106.24      
    31-Dec-1899 11:15:00              26.24                   101.45      
    31-Dec-1899 11:30:00              27.11                    97.68      

[h,m,s] = hms(T.Time);                                          % Get Time Data
T.Time = datetime([repmat([2023 2 10],numel(h),1) h m s])       % Create New Dates & Times

T = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      

DI = cumsum([0; diff(hour(T.Time))<0]);                         % Check For Midnight Rollover
T.Time = T.Time + days(DI)                                      % Increment Days For Midnight Rollovers

T = 40×3 table
            Time            Altitude [degree celcius]    Shadow length [m]
    ____________________    _________________________    _________________

    10-Feb-2023 07:45:00               1.34                   2135.8      
    10-Feb-2023 08:00:00               2.34                   808.83      
    10-Feb-2023 08:15:00               3.34                   495.25      
    10-Feb-2023 08:30:00               4.34                   357.42      
    10-Feb-2023 08:45:00               5.34                   280.44      
    10-Feb-2023 09:00:00               6.34                    231.5      
    10-Feb-2023 09:15:00               7.34                   197.77      
    10-Feb-2023 09:30:00               8.34                   173.22      
    10-Feb-2023 09:45:00              17.92                   154.65      
    10-Feb-2023 10:00:00              19.63                   140.22      
    10-Feb-2023 10:15:00              21.22                   128.79      
    10-Feb-2023 10:30:00              22.68                   119.62      
    10-Feb-2023 10:45:00              24.02                   112.22      
    10-Feb-2023 11:00:00               25.2                   106.24      
    10-Feb-2023 11:15:00              26.24                   101.45      
    10-Feb-2023 11:30:00              27.11                    97.68      

All the times are the same day here, however I included code that will increment the days in the event other data sets would have a midnight rollover. (I have used this approach with other problems.)

.

[Sanley Guerrier]

Thanks a lot!

[Star Strider]

As always, my pleasure!