readtable, excel, date base

How can I tell readtable what the appropriate excel base date is?

[Jeremy Hughes]

Hi, can you please link a copy of this file. As far as I know, readtable already checks the file for the date base. If that's not working, there might be something set incorrectly in the file.

BTW, what version of MATLAB do you see this behavior?

[Bruce]

R2020a, and I'll get you the file when I'm back at work tomorrow.

[Bruce]

Here is sample.xlsx and sample.mat. sample.mat contains the results of

data = readtable('sample.xlsx','sheet','Results','PreserveVariableNames',true);

Answer 1

[Cris LaPierre]

Perhaps with the DatetimeType name value pair. You can find details in the Name-Value Pair Arguments section of the documentation under Text and Spreadsheet files.

One of the options is exceldatenum.

Otherwise, you might consider using import options. You can use setvarops to set parameters for how datetimes are interpretted. See this example. The dateType property might provide you the setting you need.

[Bruce]

Thanks for the suggestion, but I guess I could have been a bit clearer in my question.

When I look at the date in excel, it's 2/21/2021. When I use readtable to read the data into matlab, it's 2/21/2017. The shift comes because of the "insanity" in excel where there are 2 different basedates, either 1/1/1900 or 1/1/1904.

Is there a mechanism to query the excel file to determine which basedate is being used in excel and adjust readtable accordingly?

[Cris LaPierre]

If the original question is still how to tell readtable what base date to use, then look into the dateType property I linked to above.

If the question is instead how to determine what date system the workbook is using, I'm afraid I don't know of a way to do that programmatically.

[Cris LaPierre]

Ok, I may have found a way (idea source). It relies on creating an activex connection to the workbook. Once you've done that, you can query the property. I first recorded a macro in Excel of me toggling the 1904 date system setting on then off. The macro contained the following

ActiveWorkbook.Date1904 = True

ActiveWorkbook.Date1904 = False

That gives me a hint at what property name I should try to query.

Looking on the forum, I found this post about how to create an ActiveX connection to an Excel Workbook in MATLAB. I modified the code from that post to the following.

% Specify file name
file = 'D:\Applications\MATLAB701\work\example.xls'; % This must be full path name

% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;

% Open Excel file
Workbook=Workbooks.Open(file);

% Determine if 1904 date system was used
is1904 = Workbook.Date1904

% Close Excel and clean up
invoke(Excel,'Quit');
delete(Excel);
clear Excel;

The result is a logical value (1 or 0) for is1904 indicating whether the 1904 date system is used in the indicated workbook or not. That can be used to select which dateType is used for importing the data.

Just note that the workbook must not be open in Excel when this code runs.