InputFormat not working when reading excel dates with readtable

3 次查看（过去 30 天）
Catherine
Catherine 2025-3-28
评论： Catherine 2025-3-28
I have gotten this to work using xlsread, but I see it is not recommended, so I would like to sort this issue out.
I made a simple excel file, testfile.xlsx, that has a date field that is in european time. The date is 1Sep2025. I'm trying to get it into Matlab (2023a or 2023b) using readtable.
f1 = ['Q:\testfile.xlsx'];
opts = detectImportOptions(f1);
opts = setvaropts(opts,'FieldDT','InputFormat','dd/MM/yyyy HH:mm');
opts = setvaropts(opts,'FieldDT','DateTimeFormat','MMM dd yyyy HH:mm:ss');
R1 = readtable(f1,opts)
Here is my output:
R1 =
1×4 table
FieldDT A B C
____________________ _ _ ________
Jan 09 2025 11:12:00 1 2 {'Frog'}
When I switch the InputFormat to 'MM/dd/yyyy HH:mm' it still gives me this same output, which does not make any sense to me. I have even changed the DatetimeLocale to 'de_DE' and 'en_GB' without any difference in output.

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

采纳的回答

Walter Roberson
Walter Roberson 2025-3-28
The first field of the file is not text that can be parsed. The first field of the file is in Excel numeric format, which is "seconds since Jan 1, 1900". The display formatting applied to the cell converts the numeric value to display date and time.
  1 个评论
Catherine
Catherine 2025-3-28
Well, that explains why this isn't working. It also explains why xlsread works - since it is pulling in the date cell as a string that can then be parsed with an inputFormat. Thank you.

请先登录，再进行评论。

更多回答（0 个）

类别

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

标签

产品


版本

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by