Rearranging dates to make them suitable for datetime format

1 view (last 30 days)
Hi,
I have a cell array with many dates that were imported from an excel file.
Each cell array entry has differnt inputs ie 08/21/2020 (Aug), 05/26/2018 (May), 04/03/2016, 02/06/2005 (QQ).
I just neee the dates and then import them as datetime format. How can I cut the brackets out so that I can import the file into a datetime format. Remember that some of the dates above dont have brackets.
Thanks

Accepted Answer

Stephen23
Stephen23 on 25 Aug 2020
>> C = {'08/21/2020 (Aug)';'05/26/2018 (May)';'04/03/2016';'02/06/2005 (QQ)'};
>> D = datetime(regexp(C,'^.{10}','match','once'),'InputFormat','MM/dd/yyyy')
D =
21-Aug-2020
26-May-2018
03-Apr-2016
06-Feb-2005

More Answers (1)

Steven Lord
Steven Lord on 25 Aug 2020
Edited: Steven Lord on 25 Aug 2020
If you need to import data in multiple formats, start by trying to import all the data in one of the formats. The entries that cannot be interpreted in that format will be NaT in the resulting datetime array. Fill in those NaT entries using logical indexing.
>> C = {'08/21/2020 (Aug)';'05/26/2018 (May)';'04/03/2016';'02/06/2005 (QQ)'}
C =
4×1 cell array
{'08/21/2020 (Aug)'}
{'05/26/2018 (May)'}
{'04/03/2016' }
{'02/06/2005 (QQ)' }
>> dt = datetime(C, 'InputFormat', 'MM/dd/yyyy')
dt =
4×1 datetime array
NaT
NaT
03-Apr-2016
NaT
>> dt(isnat(dt)) = datetime(C(isnat(dt)), 'InputFormat', 'MM/dd/yyyy (MMM)')
dt =
4×1 datetime array
21-Aug-2020
26-May-2018
03-Apr-2016
NaT
Repeat until there are no more NaT values in the datetime array and/or no more potential input formats to try.

Categories

Find more on Tables 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!