Cell array of dates in string format to date array

10 次查看(过去 30 天)

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!

  3 个评论
Lauren Dransfield
Lauren Dransfield 2018-10-1
编辑:Lauren Dransfield 2018-10-1
Apologies, data uploaded.
Thanks for your suggestion, but I'd prefer to avoid using tables if possible.
jonas
jonas 2018-10-1
编辑:jonas 2018-10-1
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.

请先登录,再进行评论。

采纳的回答

jonas
jonas 2018-10-1
编辑:jonas 2018-10-1
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.

更多回答(1 个)

Peter Perkins
Peter Perkins 2018-10-1
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.

类别

Help CenterFile Exchange 中查找有关 Data Type Identification 的更多信息

产品


版本

R2017a

Community Treasure Hunt

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

Start Hunting!

Translated by