Cell array of dates in string format to date array

I've pulled some data from a spreadsheet and created a new cell array from the first column, which is dates in the format 'dd/MM/yyyy' (data attached) and then I've tried to convert those to dates:

[~, ~, raw] = xlsread('file.xlsx', 'sheet');
date = {rawFull{2:end,1}}';
date2 = datetime(date, 'InputFormat', 'dd/MM/yyyy');

But I get the following error:

Error using datetime (line 629)
Input data must be a numeric or a date/time string or a cell array or char matrix containing date/time character vectors.

I've had a look using a smaller dummy array that works correctly:

dummyOld = {'01/10/2017'; '01/10/2017'};
dummyNew = datetime(dummyOld, 'InputFormat', 'dd/MM/yyyy');

I assume the difference between working and not working is something to do with the quotation marks shown below, but I'm not sure how to overcome that? Any help (with an explanation of what I'm missing) is much appreciated!

[jonas]

Please upload the data.

I would also consider readtable instead of xlsread. In my experience it is often able to recognize and parse the date format correctly even with limited user input.

[Lauren Dransfield]

Apologies, data uploaded.

Thanks for your suggestion, but I'd prefer to avoid using tables if possible.

[jonas]

No problem, if you want to avoid tables that's fine. One of the advantages with tables is that you can mix different classes, so you do not have to work with "raw data". You can easily transfer the data from a table to a struct or cell array post-import if you prefer to work with such variables.

Answer 1

[jonas]

The problem is that you have a number of NaNs at the end of your cell array. Due to those NaNs, you can not concatenate the data in a single array (you cannot mix strings and doubles). It's best to avoid this issue altogether by importing the data properly. For example, import only the relevant cells when calling xlsread or, better yet, use readtable.

Upload the xlsfile if you need more help!

If you really want to proceed with your previous approach, then find and delete the cells containing NaNs prior to calling datetime

data=load('matlabDateParseError.mat')
d=data.date;
id=cellfun(@(x)sum(isnan(x)),d)
d(logical(id))=[];
datetime(d)

don't name your variable date! It's a built in function.

[Lauren Dransfield]

Thanks, Jonas

I had something removing the NaNs afterwards so a bit of reorganising worked a treat

[jonas]

My pleasure!

Answer 2

[Peter Perkins]

Lauren, is there a reason you want to avoid using tables? You really will be much happier using readtable, especially in recent versions, even if just to do the import.