datetime conversion from Excel to MATLAB wrong

Question

Tongyao Pu 2022-9-9

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1801805-datetime-conversion-from-excel-to-matlab-wrong

编辑： Tongyao Pu 2022-9-9

采纳的回答： Tongyao Pu

Screen Shot 2022-09-09 at 8.53.34 AM.png

在 MATLAB Online 中打开

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Tongyao Pu 2022-9-9

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1801805-datetime-conversion-from-excel-to-matlab-wrong#answer_1049930

编辑：Tongyao Pu 2022-9-9

在 MATLAB Online 中打开

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:

Excel still uses 1904 date system so it is showing me the correct date (2012 -)
Switch Excel cell format to 'General' - so it will give you a date number, which means the days after 1904-01-01
Go to matlab, import the data as double
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');

2 个评论
显示无隐藏无

Walter Roberson 2022-9-9

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 2022-9-9