WHY DATE CANNOT TRANSFER FROM EXCEL TO MATLAB BY XLSREAD?

Hello everyone,

To the right sight in the photo is my excell origin and to your left sight is my code (of xlsread) to transfer the data from column A to D. However, the result was just from column B to D! I changed to use readtable and the notice was: "Unable to determine the format of the DATETIME data, try adding a format to the DATETIME specifier".

Could you please help me? I have tried numerous times and still unsucced, I don't know how to fix this problem.

Thank you.

[madhan ravi]

Attach your data.

[WHISKY Chen]

Excuse me, could you please express more? Sorry I haven't imagined clearly. Thank you

Answer 1

[Star Strider]

If you have R2013b or later, use the readtable (link) function. It should automatically convert the Excel dates to MATLAB dates.

[WHISKY Chen]

ah thank you! I used readtable to import the date and succeed! However, the result of date was in text format.

I need to merge the date and time together in order to make a plot with another parameter, do you know how to exchange into number?

Thank you thank you so much

[Star Strider]

As always, my pleasure.

To combine the dates and times into one variable, use the join (link) function.

Example —

C = {'07.03.2019' '20:30' 'a'
     '07.03.2019' '20:31' 'b'
     '07.03.2019' '20:32' 'c'
     '07.03.2019' '20:33' 'a'
     '07.03.2019' '20:33' 'b'
     '07.03.2019' '20:34' 'c'};                             % Original Date & Time Data
Tbl = cell2table(C);                                        % Convert To Table For This Illustration

Tbl.Date_Time = join([Tbl.C1, Tbl.C2])                      % Create New ‘Date_Time’ Variable From ‘C1’ & ‘C2’
Tbl.C1 = [];
Tbl.C2 = []                                                 % Delete ‘C1’ & ‘C2’, Retain ‘Date_Time’ & ‘C3’
Tbl = movevars(Tbl, 'C3','After','Date_Time')               % Rearrange Columns
DN = datenum(Tbl.Date_Time)                                 % Convert ‘Date_Time’ To A Separate ‘datenum’ Vector 

This creates a table from some other data, then shows how to combine the date and time variables into one variable. Run it to see the effect of the different function calls, and the final result.

You can easily plot your table using the datetime variable created in ‘Date_Time’. If you need to convert it to a double array of datenum values, the ‘DN’ variable does that.

[WHISKY Chen]

Really really thank you!

Based on your help, I can run it successfully! Thank you thank you!

[Star Strider]

As always, my pleasure!

Answer 2

[Peter Perkins]

As SS says, in recent versions of MATLAB, readtable should give you a table with a datetime and a duration for those columns in your spreadsheet. If readtable does not do that, you are either using a version of MATLAB prior to that behavior (R2018a, IIRC), or your spreadsheet is not formatted properly. It's impossioble to tell because you have provided only screenshots.

In any case, use readtable. If the table contains text, convert to datetime and duration. Probably something like

T.DateTime = datetime(T.Date) + duration(T.Time);

The datetime conversion should work all the way back to R2014, the duration converstion is newer, but you can substitute text2duration form the File Exchange.

[WHISKY Chen]

Oh so thank you. The Datetime can also use in R2018, thank you!

The text2duration I haven't known before, let me try... thank you again!