How to avoid "NaT" when converting a cell into datetime ? (Or: how to add missing parts of datetime ?)

How to avoid "NaT" when converting a cell into datetime ?

(Or: how to add missing parts of datetime ?)

a = [
    {'14-Jul-2021 17:00:00'}
    {'15-Jul-2021 12:00:00'}
    {'16-Jul-2021 21:00:00'}
    {'17-Jul-2021'         }
    {'18-Jul-2021 06:00:00'}
    {'19-Jul-2021 04:00:00'}
    {'20-Jul-2021 17:00:00'}
    {'21-Jul-2021 08:00:00'}]

a = 8×1 cell array
    {'14-Jul-2021 17:00:00'}
    {'15-Jul-2021 12:00:00'}
    {'16-Jul-2021 21:00:00'}
    {'17-Jul-2021'         }
    {'18-Jul-2021 06:00:00'}
    {'19-Jul-2021 04:00:00'}
    {'20-Jul-2021 17:00:00'}
    {'21-Jul-2021 08:00:00'}

datetime(a)

ans = 8×1 datetime array
   14-Jul-2021 17:00:00
   15-Jul-2021 12:00:00
   16-Jul-2021 21:00:00
   NaT                 
   18-Jul-2021 06:00:00
   19-Jul-2021 04:00:00
   20-Jul-2021 17:00:00
   21-Jul-2021 08:00:00

As workaround, how to add a representative hour (e.g. '12:00:00') where it is missing, in order to avoid a "NaT" ?

Answer 1

[Stephen23]

"How to avoid "NaT" when converting a cell into datetime ?"

Convert each cell individually. Certainly not as efficient as one DATETIME call, but it is fairly simple:

C = {...
    '14-Jul-2021 17:00:00'
    '15-Jul-2021 12:00:00'
    '16-Jul-2021 21:00:00'
    '17-Jul-2021'
    '18-Jul-2021 06:00:00'
    '19-Jul-2021 04:00:00'
    '20-Jul-2021 17:00:00'
    '21-Jul-2021 08:00:00'}; % one cell array :)
D = cellfun(@datetime,C)

D = 8×1 datetime array
   14-Jul-2021 17:00:00
   15-Jul-2021 12:00:00
   16-Jul-2021 21:00:00
   17-Jul-2021 00:00:00
   18-Jul-2021 06:00:00
   19-Jul-2021 04:00:00
   20-Jul-2021 17:00:00
   21-Jul-2021 08:00:00

[Sim]

Cool! Many thanks @Stephen23 ! :-)

Btw, do you know how to add a custom hour instead of '00:00:00' ?

something like this ?

cellfun(@datetime,C,'custom','12:00:00')

[Stephen23]

"Btw, do you know how to add a custom hour instead of '00:00:00' ?"

Probably the simplest approach is to just add it to the datetime array:

C = {...
    '14-Jul-2021 17:00:00'
    '15-Jul-2021 12:00:00'
    '16-Jul-2021 21:00:00'
    '17-Jul-2021'
    '18-Jul-2021 06:00:00'
    '19-Jul-2021 04:00:00'
    '20-Jul-2021 17:00:00'
    '21-Jul-2021 08:00:00'}; % one cell array :)
D = cellfun(@datetime,C);
D(4) = D(4) + hours(12)

D = 8×1 datetime array
   14-Jul-2021 17:00:00
   15-Jul-2021 12:00:00
   16-Jul-2021 21:00:00
   17-Jul-2021 12:00:00
   18-Jul-2021 06:00:00
   19-Jul-2021 04:00:00
   20-Jul-2021 17:00:00
   21-Jul-2021 08:00:00

[Sim]

ah ok, thanks a lot, perfect!

.......but the thing is that the position can change... I mean, for this array, the missing part is at the 4th element, but it can be at any element, the 2nd, the 15th, the 29th, etc... :-)

[Stephen23]

"but the thing is that the position can change"

Of course, you can use any indexing you want, you are not restricted to just using index 4. For example, if you want to detect all character vectors shorter than 12 characters and add 12 hours to the corresponding datetime:

C = {...
    '14-Jul-2021 17:00:00'
    '15-Jul-2021 12:00:00'
    '16-Jul-2021 21:00:00'
    '17-Jul-2021'
    '18-Jul-2021 06:00:00'
    '19-Jul-2021 04:00:00'
    '20-Jul-2021 17:00:00'
    '21-Jul-2021 08:00:00'}; % one cell array :)
D = cellfun(@datetime,C);
X = cellfun(@numel,C)<12; % or whatever logic you want to use (which you did not explain)
D(X) = D(X) + hours(12)

D = 8×1 datetime array
   14-Jul-2021 17:00:00
   15-Jul-2021 12:00:00
   16-Jul-2021 21:00:00
   17-Jul-2021 12:00:00
   18-Jul-2021 06:00:00
   19-Jul-2021 04:00:00
   20-Jul-2021 17:00:00
   21-Jul-2021 08:00:00

[Sim]

Cool, thanks!! :-)

The logic of indexing the missing parts of datetime elements by using the "characters length of those datetime elements" (i.e. 11 characters in '17-Jul-2021'), works well in my case, many thanks !