MATLAB: Datevec text format problems

date stringdatevecxlsread

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

Best Answer

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.
Related Question