datetime conversion from Excel to MATLAB wrong

24 次查看（过去 30 天）
Tongyao Pu
Tongyao Pu 2022-9-9
编辑： Tongyao Pu 2022-9-9
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.

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

采纳的回答

Tongyao Pu
Tongyao Pu 2022-9-9
编辑：Tongyao Pu 2022-9-9
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');
  2 个评论
Walter Roberson
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
Tongyao Pu 2022-9-9
编辑：Tongyao Pu 2022-9-9
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

请先登录，再进行评论。

更多回答（1 个）

Walter Roberson
Walter Roberson 2022-9-9
编辑：Walter Roberson 2022-9-9
datetime.setDefaultFormats('default','yyyy/MM/dd hh:mm');
hh is for 12 hour day. You need HH for 24 hour day.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by