datetime conversion from Excel to MATLAB wrong

Problem:

when read excel into MATLAB, the date that is supposed to be 2012 gets translated into 2008

What I have:

an excel file, which only has one sheet.

What I did:

datetime.setDefaultFormats('default','yyyy/MM/dd hh:mm');
opts = detectImportOptions('MyExcelFile.xlsx');
RC_table = readtable('MyExcelFile.xlsx',opts);

What MATLAB gave me:

What else have I tried:

I tried using different date format in excel and correspondingly in MATLAB. It gives me the same problem.

I also tried in Excel, switch to the generic format. So the first date (2012/8/5 10:00) turns into 39664.4166666667. Then I say something like

t_datetime = datetime(39664.4166666667 + datenum(1900,01,01), 'ConvertFrom', 'datenum')

Somehow MATLAB thinks it is 2008/08/06 10:00 while excel thinks it is 2012/8/5 10:00

I also made sure I was reading the correct excel document.

Answer 1

[Tongyao Pu]

After a simpler trial run with excel, I identified the problem: my excel is using the 1904 date system (the default date number for excel starts at 1904).

This is still intriguing for me that MATLAB imports the excel information as date number although in excel I already clearly specified the cell formate is a date format of yyyy/MM/dd

Solution:

You could change excel to 1900 date system but that only results in my data in excel switch to 2008. I prefer doing this in MATLAB.

what I did:

1. Excel still uses 1904 date system so it is showing me the correct date (2012 -)
2. Switch Excel cell format to 'General' - so it will give you a date number, which means the days after 1904-01-01
3. Go to matlab, import the data as double
4. convert datenum to datetime with the code below:

RC_datenum = table2array(RC_table(:,2)) + datenum(1904, 01, 01); % MAC excel default 1904 system
RC_time = datetime(RC_datenum, 'ConvertFrom', 'datenum');

[Walter Roberson]

Excel dates are stored as number of days and fraction of days since a start point, not as text. MATLAB grabs the number and does a datetime() 'convertfrom', 'excel' . I suspect there is a way of changing the options for the variable to use 'excel1904' for the conversion.

[Tongyao Pu]

This makes more sense. I didn't find the 1904 import option in MATLAB. I just found Mathworks suggested doing calculations in MATLAB. https://www.mathworks.com/help/exlink/convert-dates-between-microsoft-excel-and-matlab.html

Answer 2

[Walter Roberson]

datetime.setDefaultFormats('default','yyyy/MM/dd hh:mm');

hh is for 12 hour day. You need HH for 24 hour day.

[Tongyao Pu]

yep, I did not even notice this