Why Matlab doesn't recognize the time variable from .xlsx file

I need to have 1 information per second of the time, which is why i have to compare times and remove the unnecessary ones. But Matlab doesn't recognize the column with the Time variable, even though the formating in excel is done right. Can anyone help with this ?

[Rik]

What exact code are you using to read this excel file? Excel can be very picky about the locale settings. To reproduce your issue it may also be needed to know the language you have your OS set to (and the date/time settings), as well as the language your Office is set to. Thanks Microsoft...

Answer 1

[Star Strider]

I have no idea why datetime cannot import the time correctly in that format. The readtable function imports it a fractions of a day.

A small diversion into datevec and it works:

T1 = readtable('test.xlsx');
T1.Time = datetime(datevec(T1.Time), 'Format','HH:mm:ss');                  % Original Table
T2 = table(T1.Date + timeofday(T1.Time), 'VariableNames',{'Date_Time'});    % Create New Variable
T2 = [T2 T1(:,3:end)]                                                       % Derived Table

and:

Sample = T2(1:10,:)

displays:

Sample =

  10×3 table

         Date_Time          Angle    Speed
    ____________________    _____    _____

    01-Feb-2014 00:00:00     62       3.6 
    01-Feb-2014 00:00:00     62       3.8 
    01-Feb-2014 00:00:01     60       4.1 
    01-Feb-2014 00:00:01     59       4.2 
    01-Feb-2014 00:00:02     58       4.3 
    01-Feb-2014 00:00:02     60       4.3 
    01-Feb-2014 00:00:02     64       4.3 
    01-Feb-2014 00:00:03     63       4.3 
    01-Feb-2014 00:00:03     61       4.3 
    01-Feb-2014 00:00:04     61       4.3 

The original ‘T1’ table keeps the dates and times in separate columns. The ‘T2’ table concatenates them.

.

[Viktor G.]

Sample =

  10×3 table

    Date_Time    Angle    Speed
    _________    _____    _____

    2/1/2014      62       3.6 
    2/1/2014      62       3.8 
    2/1/2014      60       4.1 
    2/1/2014      59       4.2 
    2/1/2014      58       4.3 
    2/1/2014      60       4.3 
    2/1/2014      64       4.3 
    2/1/2014      63       4.3 
    2/1/2014      61       4.3 
    2/1/2014      61       4.3 
    

not sure why it doesn't work for me.... maybe it has something to do with the windows settings :/ however thank you very much

[Rik]

That looks like the same date, so if you want to show the complete time there might be a display setting in Matlab.

[Star Strider]

Viktor G. — I am using R2020a (Update 3). There have been a few changes in table and datetime over the years. I no longer have access to any earlier than R2019b here. The ‘T1’ version should work, even if ‘T2’ does not.

Rik — Thank you!

.

[Viktor G.]

unfortunately it doesn't work because it adds the today's date. look at the prview when i save the table as T1

[Star Strider]

It does work, and it does not add today’s date in the code I posted for ‘T2’. It creates a new datetime array with the first variable combining the original date and the time. (The ‘T1’ table keeps them separate because I do not know which version you want.) Additionally:

T1.Time = datetime(datevec(T1.Time), 'Format','HH:mm:ss');

specifies that only the hours (as two-digit 24-hour time), minutes, and seconds are to appear in the output.

I do not know what MATLAB version you are using. As I said, the code I posted works correctly in R2020a. Be sure you have all the available updates for your version.

If it did not work, I obviously would not have posted it. I do not post non-working code.

[Viktor G.]

What I meant obviously is that it doesn't work for me and that is probably because of a windows setting or somethig. No need for a snarky answer. Thank you for your help :)

[Star Strider]

As always, my pleasure!

I have no control over any Windows settings other than my own.