Importing Time data from excel.

I am trying to read an excel file containg the date and time in the format "dd/mm/yyyy HH:MM:SS". (The excel contains five column [date, data1, data2, data3, data4])

I read the excel file with following code

[num,text,both]=(xlsread('file_name'));     

and extracted date from it using

data_only = both(:,1);
s=datenum(data_only ,'dd-mm-yyyy HH:MM');
date=datestr(s,'dd-mm-yyyy HH:MM:SS');

My date starts with 25-03-2019 00:00:00 and increase with time step of 15 minute (ie 25-03-2019 00:00:00, 25-03-2019 00:15:00, 25-03-2019 00:30:00.....

The cell with date "25-03-2019 00:00" in excel ie at 12 am (00:00:00) is reading as "25-03-2019" only in matlab and gave error in line 3 of the code. When i ignored the first value by modifing the line 2 as

data_only  = both(2:end,1);

ie date start from "25-03-2019 00:15" i am getting the required result.

Error message was

Error using datenum (line 181)
DATENUM failed.

Error in file_name (line 3)
s=datenum(data_only ,'dd-mm-yyyy HH:MM');

Caused by:
    Error using dtstr2dtnummx
    Failed to convert from text to date number.

I have tried with format as "date" and custom format of dd-mm-yyyy HH:MM while saving the excel file.

[Bob Thompson]

Does datetime work? In place of datenum.

[anooja thomas]

Yes it worked. Thank You

Answer 1

[Cris LaPierre]

I find readtable works best with Excel files.

opts = detectImportOptions('file_name.xlsx');
data = readtable("file_name.xlsx",opts)

Where your first column is time data, consider converting your table to a timetable.

data = table2timetable(data)

The problem with your code is the format changes from row 1 to row 2. When trying to manually convert, you have to handle both cases. Better to use readtable if you can.

[Akira Agata]

If you are using the latest MATLAB (R2019a), you can use newly introduced readtimetable function to do it, like:

TT = readtimetable('file_name.xlsx');

[anooja thomas]

Thankyou.. It worked.