Add some colums on the right date

>> data(1:5)

ans =

1×5 cell array

Column 1

    'Zeit'

Column 2

    '11.07.2016 11:40:03'

Column 3

    '11.07.2016 11:45:05'

Column 4

    '11.07.2016 11:50:05'

Column 5

    '11.07.2016 11:55:04'
... etc.

and

>> LA(1:5)

ans =

1×5 cell array

Columns 1 through 2

    []    '11:07:2016 00:00:00'

Column 3

    '11:07:2016 00:05:00'

Column 4

    '11:07:2016 00:10:00'

Column 5

    '11:07:2016 00:15:00'

... etc

I want to know the line of the nearest date.

date1=datenum(data(2:end,1),'dd:MM:yyyy HH:mm:ss');
date2=datenum(LA(2:end,1),'dd:MM:yyyy HH:mm:ss');
t1=datenum(data(2,1);'dd:MM:yyyy HH:mm:ss');
  [~,s1] = min(abs(date1-t1));
  [~,s2] = min(abs(date2-t1));
closest_time = data(s1+1,1)
closest_time = LA(s2+1,1)

>> date1=datenum(data(2:end,1),'dd:MM:yyyy HH:mm:ss');
Error using datenum (line 181)
DATENUM failed.

Caused by:
    Error using dtstr2dtnummx
    Failed to convert from text to
    date number.

but date2 works!

Where is the error?

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- I have many xls-files and I want to calculat the value in the second colums in each xls-file but on the same time. The problem is, that the time (second) ist not exaktly the same in each file. Maybe someone hat a better solution. Maybe I can plot all the xls-files and Matlab add the value automatical. But how I do that?

Answer 1

[Guillaume]

Well, clearly from the error message, one of the column of data does not contain a valid date. Without seeing the data it' impossible for us to tell you which column(s) is a problem. Perhaps:

faultycolumn = 1 + find(cellfun(@isempty, regexp(date(2:end, 1), '^\d{2}:\d{2}:\d{4} \d{2}:\d{2}:\d{2}$', 'once')))

will tell you which column is faulty (no guarantee, the regexp only test for a small subset of invalid strings).

Also, note that matlab has had the more useful datetime since R2104b. I recommend using that instead of datenum (won't fix the problem though).

[Flo Hem]

Thank you for your answer. I took it, but faultycolumn is the whole colomn. It is every line, where the date is, is in faultycolumn. How help me that? The column LA & column data are both cell and have date on every line. But with column ''LA'' datenum works and on column() ''data'' datenum() doesn't work. Why? It seems be the same! How I can fix it? I work with Matlab R2016b. How looks the better solution with datetime?

[Flo Hem]

>> [~, ~, data]=xlsread(filename); >> date1=datenum(data(:,1),'dd:MM:yyyy HH:mm:ss'); Error using datenum (line 181) DATENUM failed.

Caused by: Error using dtstr2dtnummx Failed to convert from text to date number.

>> [temp, timestamps] = xlsread(filename, 'Tabelle1', 'A2:A50000'); >> date1=datenum(timestamps(:,1),'dd:MM:yyyy HH:mm:ss'); Error using datenum (line 181) DATENUM failed.

Caused by: Error using dtstr2dtnummx Failed to convert from text to date number.

I try it on two ways. But it doesn't work. What I have do to with the xls-file, that it works?

[Guillaume]

Doh! I made a mistake in the regular expression. Try:

faultycolumn = 1 + find(cellfun(@isempty, regexp(data(2:end, 1), '^\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}$', 'once')))

[Flo Hem]

>> faultycolumn = 1 + find(cellfun(@isempty, regexp(data(2:end, 1), '^\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}$', 'once'))) MATLAB has lost its connection to the license server. MATLAB will make 9 more reconnection attempt(s) and then exit. Any unsaved changes will be lost.

faultycolumn =

0×1 empty double column vector

And now?

[Guillaume]

I think I've just realised what the problem is. I actually hadn't made a mistake in my original regular expression. I based it on the conversion string you passed to datenum whereas the second regular expression is based on the actual string in your cell array. So the original faultycolumn was correct in regards to your conversion string.

Your two cell arrays use different format for the date. One use a . to separate days month years. The other use :. However, you pass the same conversion string for both, so it's never going to work.

%The two conversion strings below are purposely different as the 
%inputs actually use different format.
date1 = datenum(data(2:end, 1), 'dd.MM.yyyy HH:mm:ss');  
date2=datenum(LA(2:end,1),'dd:MM:yyyy HH:mm:');

should fix the problem.

Morale of the story: Use consistent format in your inputs. It will save a lot of headache.

[Flo Hem]

Thanks a lot. It works:D