Reading date-time data stored as Excel format into MATLAB

Question

I have a csv file which contains a column for data-time in Excel format, and two other columns which are floats; The Excel format date includes the number of days since 01-Jan-1900, so for example, 2 means 02-Jan-1900, and 365 means 30-Dec-1900, and so on.

The file looks like the following:

date,temp,value
41909.89,49.67,32
41910.67,49.16,36
41911.37,45.33,37

What is the best way to read this file and convert the date to datetime format in MATLAB?

Did you already google "Excel date Matlab" ? The first hit (for me anyway) is from the Mathworks site and explains exactly what you need to do. — Tim Williams, Oct 12 '17 at 04:11

score 2 · Accepted Answer · answered Oct 12 '17 at 04:13

Use readtable to read the file, then convert the dates using datetime. If you have the Financial Toolbox then you can do the conversion from Excel date numbers to MATLAB date numbers using x2mdate. If not then the offset is dependent on the date format you are using in Excel. See the doc for x2mdate for details of possible offsets.

>> tbl = readtable('datafile.txt')
tbl =
  3×3 table
      date      temp     value
    ________    _____    _____
    41909.89    49.67    32   
    41910.67    49.16    36   
    41911.37    45.33    37   
>> tbl.date = datetime(tbl.date+693960,'ConvertFrom','Datenum')
tbl =
  3×3 table
            date            temp     value
    ____________________    _____    _____
    27-Sep-2014 21:21:36    49.67    32   
    28-Sep-2014 16:04:48    49.16    36   
    29-Sep-2014 08:52:48    45.33    37

Reading date-time data stored as Excel format into MATLAB

1 Answers1