InputFormat not working when reading excel dates with readtable

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.

Answer 1

[Walter Roberson]

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.

[Catherine]

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.