I have a problem with transforming date vector from excel in matlab

I have the following code:

dn= datenum(DatumUhrzeit , 'dd.mm.yyyy' HH:MM:SS')

Now, I have the problem , that the excel sheets sometimes have issues like that:

03.06.2017 11:00
03.06.2017
03.06.2017 12:00

what I mean: sometimes the time is missing. In that case, the code above obviosly does not work for that line. I could solve that problem by editing my excel sheet but is there a way to do that in matlab?

Answer 1

[Guillaume]

One possible workaround:

DatumUhrzeit = regexprep(DatumUhrzeit, '\d+\.\d+\.\d+\s*$', '$0 00:00');
dn = datenum(DatumUhrzeit , 'dd.mm.yyyy HH:MM')

Note that your original input format (with :SS) does not match your example (no seconds).

You should be using datetime instead of datenum if you've got any reasonably recent version of matlab. datetime is a lot more practical for date calculations.

The regular expression looks for numbers.numbers.numbers followed by optional blanks and the end of the string (so no time) and add to that a time of 00:00.

[Lidiya P]

Hi,

sorry, that was my mistake. Actually I do have seconds in my excel sheet, that#s definitely not the problem because it works if I edit my excel sheet first.

I've tried your code and it does work, thank you, can you tell me what excactly ist does?

[Guillaume]

As I more or less explained, it uses a regular expression to find the strings that do not have time and add a time of 00:00 to these. If you have seconds, the replacement string is '$0 00:00:00', so

DatumUhrzeit = regexprep(DatumUhrzeit, '\d+\.\d+\.\d+\s*$', '$0 00:00:00');