MATLAB Answers

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

1 view (last 30 days)
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
  2 Comments

Sign in to comment.

Accepted Answer

Stephen
Stephen on 24 Aug 2021
Edited: Stephen on 24 Aug 2021
There is no tool which will correctly interpret the mutually-exclusive date formats used around the world:
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
  1 Comment
Emanuel Gunnarsson
Emanuel Gunnarsson on 24 Aug 2021
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!

Sign in to comment.

More Answers (1)

Yongjian Feng
Yongjian Feng on 24 Aug 2021
  2 Comments
Emanuel Gunnarsson
Emanuel Gunnarsson on 24 Aug 2021
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

Sign in to comment.

Products


Release

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by