MATLAB Answers

readtable, excel, date base

33 views (last 30 days)
Bruce
Bruce on 3 Mar 2021
Commented: Bruce on 4 Mar 2021
How can I tell readtable what the appropriate excel base date is?
  3 Comments
Bruce
Bruce on 4 Mar 2021
Here is sample.xlsx and sample.mat. sample.mat contains the results of
data = readtable('sample.xlsx','sheet','Results','PreserveVariableNames',true);

Sign in to comment.

Answers (1)

Cris LaPierre
Cris LaPierre on 3 Mar 2021
Edited: Cris LaPierre on 3 Mar 2021
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.
  3 Comments
Cris LaPierre
Cris LaPierre on 3 Mar 2021
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.

Sign in to comment.

Products


Release

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by