datevec text format problems

I am trying to read in time series from an excel spreadsheet. This contains UK format date & time in the first column.

I load this using the command

[data,txt,raw] = xlsread(filename, 'Data', 'A12:AU1451');

The date column appears as a cell array in the txt cell array, which shows the following values. Note that the midnight value doesn't contain a time part:

My problem is this - if I then convert this array using datevec:

Tm = datevec(txt);

The datevectors swap day and month around, because they assume US-style dates. If I use the format string:

Tm = datevec(txt,'dd/mm/yyyy HH:MM:SS');

then datevec throws an error, because the midnight date doesn't have its time values.

Error using dtstr2dtvecmx
Failed on converting date string to date number.
Error in datevec (line 117)
            y = dtstr2dtvecmx(t,icu_dtformat);

So how can I get a correct datevec conversion in this situation? I do need both the date and time values. I'm loading several hundred spreadsheets, and it's not practical to modify these at source.

Thanks, John

Answer 1

[dpb]

Not sure if there's a way to force the read or not but a fixup could be

L=length('01/01/0000 00:00:00');  % saves me counting chars.. :)
ix=cellfun(@length,txt)<L;
ds(ix)=strcat(ds(ix),' 00:00:00')

then do the conversion.

Alternatively, try the '%D' format string and the new(er) datetime object instead of date numbers; maybe it's more forgiving; I don't know and don't have the release to be able to test.

[John Wood]

Thanks dpb I had been hoping there might be a rather less fussy way of importing date & time strings from Excel, but in the end I've had to go with your solution and go through correcting every incorrectly formatted entry. A cleaner way of doing this would be to force xlsread() to return excel serial numbers, but that option doesn't seem to be on offer. Cheers, John

[dpb]

That's getting into the Excel side more than Matlab methinks, but if you change the format of the value to numeric instead of date it doesn't "take" and read as numeric? Of course, that's munging on the spreadsheet and is also a kluge as well...

[Peter Perkins]

John, both points well-taken. If you have access to R2016b, you'll find better support for importing dates from Excel, via readtable. Prior to R2016b, you should be able to use basic mode to read excel serial date numbers. You can convert those to datetime using

datetime(dates,'ConvertFrom','Excel')

Hope this helps.