How to import dates from different excel versions

When a date is listed by different users in a similar way ‘2017-01-30’ in excel it is still imported in Matlab in a different way, probably due to different excel versions?

Answer 1

[Walter Roberson]

Excel stores dates in three different ways:

• number of days and fraction of days since Jan 1, 1900. This has a bug in that it considers February 29, 1900 to be a leap year, which it was not
• number of days and fraction of days since Jan 1, 1904.
• As strings

When xlsread() and readtable() are able to connect to Excel on MS Windows, the numeric dates are converted into strings by the interface. This does not occur automatically for Linux or Mac, or on MS Windows if Excel cannot be contacted (e.g., is not installed.)

readtable() is also able to convert excel numeric format or times specified as strings into datetime() objects; however sometimes you might have to tell it that the field is Excel format or that it is a string to be interpreted as a date. In recent versions there is detectImportOptions() that tries harder to get the details right, and you can also fine tune the properties after detection and then pass the description to readtable()

[Leslie]

Thanks for this detailed explanation of Excel dates and the xlsread() and readtable() actions on various platforms, including the existence of detectImportOptions.

Could you be a little more explicit about readtable() import options? I'm working on a Mac, MATLAB R2018a & Excel v16.15, with a spreadsheet that has dates only (no times). When I use readtable() while the Excel worksheet is using 1900 dating, the resulting MATLAB table looks fine; when the Excel worksheet is using 1904 dating, the table dates are wrong. However, the 1900 dating messes up other portions of the table and I'd really prefer to keep the spreadsheet in 1904 dates.

I've called detectImportOptions, looked at VariableOptions, and then done the following:

>> varOpts = getvaropts(opts, 'Date_UTC_')
varOpts = 
  DatetimeVariableImportOptions with properties:

     Variable Properties:
                Name: 'Date_UTC_'
                Type: 'datetime'
           FillValue: NaT
      TreatAsMissing: {}
           QuoteRule: 'remove'
            Prefixes: {}
            Suffixes: {}
     Datetime Options:
      DatetimeFormat: 'default'
      DatetimeLocale: 'en_US'
         InputFormat: ''
            TimeZone: ''

In cross-referencing documentation for readtable(), datetime(), and setvaropts(), I cannot figure out how to tell readtable() to bring in "1904" dates instead of "1900" dates.

[Walter Roberson]

What you might need to do is to tell it the variable type is numeric. Then once the data is in the table, then you can use datetime() with 'convertfrom' of either 'excel' or 'excel1904' .

You will not be able to directly replace the entries in the table with the datetime value by using

TheTable.Date_UTC_ = datetime(....)

as that and the equivalent

TheTable{:,'Date_UTC_'} = datetime(....)

and

TheTable{:, columnnumber} = datetime(....)

all compare the incoming value to the datatime of the variable and refuse if they are incompatible. You would need to replace the entire variable entry, like

TheTable(:, 'Date_UTC_') = table(datetime(....), 'VariableNames', {'Date_UTC_'});