Date format changes at midnight

Hey, I am reading an excel log file. In the first column (as I see it in Excel) the date format is 'dd/MM/YYYY HH:mm:ss'

example: '27/12/2017 22:15:00'

When I read the xls:

 [A,rawData,All] = xlsread(FileName,'Sheet1');

I get the the same format:

rawData(1,1) = {'27/12/2017 22:15:00'}

EXCEPT when the hour is 00:00:00, then I get

rawData(2,1) = {'27/12/2017'}

Which is a problem later on, when I try to divide the data into 15 min, hourly, daily and weekly buckets when I use

datevec, datetime, datenum

I could, in theory, perform a loop to look for such instances and add the 00:00:00 manually, but my data relativaly large (around 300,000 samples).

Any suggestions?

Answer 1

[Peter Perkins]

In recent versions of MATLAB, don't use xlsread or datenum. Use readtable, which will create a datetime in the table, without the issues that Excel's date handling creates.

In earlier versions of MATLAB, you can still use readtable, but the specifics depend on the version.

[dpb]

Good point, Peter...I had restricted to not changing OPs starting point, but often that's the better solution by far to avoid the need for a later fixup earlier...

I do still think both datenum and datetime should be able to parse the above input as it exists, however...just seems rude and unexpected behavior as is. Don't know just how much overhead it would cause in the normal case, but the error-checking is being done anyway so doesn't seem like the fixup would cost much extra...

[Omer Kaspi]

Didn't know the diff between readTable and readxls until now so there was no real constraint.

Walter's solutions seems pretty nice too (and elegant), though readTable saves the effort.

Thanks all!

Answer 2

[dpb]

It's unfortunate the ML time functions aren't more forgiving of such cases.

Best I can think of otomh is to either do the fixup on the input array by testing for length and adding the trailing string or do the processing in a loop with a try...catch...end block; you put the format string with the time string in the try section and the one without in the catch.

Oh, another way that's a little cleaner code-wise; don't know how it would compare in run time...

>> dn=zeros(size(rawData));                   % preallocate output datenum vector
>> ixtim=(cellfun(@length,rawData))>10;       % logical addressing vector elements with time
>> dn(ixtim)=datenum(rawData(ixtim),'dd/mm/yyyy HH:MM:SS');  % ones with time string
>> dn(~ixtim)=datenum(rawData(~ixtim),'dd/mm/yyyy');          % those without
>> datestr(dn)                                                % what we got...
ans =
27-Dec-2017 22:15:00
27-Dec-2017 00:00:00
>>

[Walter Roberson]

rawData(:,1) = cellstr(datestr(cellfun(@datenum, rawData(:,1)),'dd/mm/yyyy HH:MM:SS'));

[dpb]

Clever way to do the text fixup, Walter. Didn't take the time to compare for large array sizes the cost of the datenum, datestr pair compared to the lookup; there are two datenum calls there as well so probably nothing significant difference-wise.