- 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?
How do I reformat a set of differently formatted dates in to one consistent format?
5 views (last 30 days)
Show older comments
Emanuel Gunnarsson
on 24 Aug 2021
Edited: Emanuel Gunnarsson
on 25 Aug 2021
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
Stephen23
on 24 Aug 2021
Edited: Stephen23
on 24 Aug 2021
"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:
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.
Accepted Answer
Stephen23
on 24 Aug 2021
Edited: Stephen23
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)
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
More Answers (1)
Yongjian Feng
on 24 Aug 2021
2 Comments
Yongjian Feng
on 24 Aug 2021
It doesn't seem like those two formats are supported. Parse the string yourself?
See Also
Categories
Find more on Logical in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!