How do I reformat a set of differently formatted dates in to one consistent format?

I have a set of data (a cell array) where one column consist of dates, now these dates have been entered in a variety of ways, here is how it looks:

'1/1/1999'

'2/1/1999'

'3/1/99'

'Feb 2001'

'unknown'

'4-2001'

'5/2000'

What I would like to do is to convert all these to one uniform format. For the first three entries it works to do the following:

datestr(char(tableOfData(1:3,:)))

For the 'unknown' I can do strcmp(char(tableOfData(5,:)),'unknown') and replace with NaT or NaN or 0 or something.

But how do I deal with the rest of the entries?

Thank you in advance!

Emanuel

[Stephen23]

"But how do I deal with the rest of the entries?"

There is no easy answer to that, because dates are written and interpreted differently by different people:

• Is '2/1/1999' the first of February (USA) or the second of January (most of the rest of the world)?
• Is '3/1/99' the same year when Narcissus of Jerusalem was born, or is there an unwritten century and millenium?

You need to decide how to handle such conflicting cases: https://en.wikipedia.org/wiki/Date_format_by_country

Ambigous input data makes processing it much more difficult.

[Emanuel Gunnarsson]

Hi Stephen!

Yes, I totally agree with you. It is a nuisance.

Answer 1

[Stephen23]

There is no tool which will correctly interpret the mutually-exclusive date formats used around the world:

https://en.wikipedia.org/wiki/Date_format_by_country

The only reliable date format is ISO 8601: https://xkcd.com/1179/

You can easily convert a subset of formats that you specify:

C = {'1/1/1999';'2/1/1999';'3/1/99';'Feb 2001';'unknown';'4-2001';'5/2000'};
T = cellfun(@myfun,C)

T = 7×1 datetime array
   01-Jan-1999
   02-Jan-1999
   03-Jan-1999
   01-Feb-2001
   NaT        
   01-Apr-2001
   01-May-2000

function T = myfun(D)
T = NaT;
for f = ["dd/MM/yy","MM/yy","MM-yy","MMM yy"] % create this list to suit your data
    try
        T = datetime(D, 'InputFormat',f);
        break
    end
end
end

[Emanuel Gunnarsson]

Oh, wow! I wasn't aware that I could use 'try', and I hadn't thought about making a function, I was just going for a 'for' loop with 'if' statements.

This seems to do the trick, I will try it and then hopefully accept your answer.

Thank you!

Emanuel

Update: Yes it worked as a charm, thank you Stephen!

Answer 2

[Yongjian Feng]

Check this:

https://www.mathworks.com/help/matlab/ref/datestr.html

[Yongjian Feng]

It doesn't seem like those two formats are supported. Parse the string yourself?

[Emanuel Gunnarsson]

Thank you for the tip Yongjian!

I had a look at the table of predefined formats and it is like you say, those latter ones doesn't seem to be supported.

How do you mean I should parse it? Using regexp in some way?

Cheers

Emanuel