How can I make a logical array by comparing two date vectors?

Hello, could you please help me on the following issue. I imported an Excel file containing the vector of dates (44 by 1, type - cell):

[temp, holidays]=xlsread('Holidays.xlsx', 'Tabelle1', 'A1:A44').

Then, I created the date vector with a step of 1 minute:

start_date = datenum('18-Apr-2015 16:30:00');
end_date = datenum('19-Apr-2015 10:00:00');
interval = 10/24/60;
date = datestr(start_date:interval:end_date);

My goal is to create a logical array that puts 1 if the date from the second (longer) vector equals the date in the first vector and 0 otherwise.

I tried the code below, but I have problems with data formats, which I cannot solve. Error using datenum (line 179) DATENUM failed.

Caused by: Error using dtstr2dtnummx Failed on converting date string to date number.

criteria=[];
for i=1:length(date)
    if day(holidays(i))==day(date(i)) && month(holidays(i))==month(date(i)) && year(holidays(i))==year(date(i))
        criteria(i)=1
    else
        criteria(i)=0
    end
end

Can please anyone help on that? Thanks a lot in advance!

[Azzi Abdelmalek]

You can make your question clear by posting a sample of your imported data.

[Ekaterina Serikova]

Ok, please, find attached. I want to compare the dates in the attached file with the dates generated by the code indicated in my question. Thank you in advance!

Answer 1

[Stephen23]

You could use ismember with the 'rows' option. It only takes two steps

1. convert all of the dates into datevectors (not datenumbers) using datevec.
2. call ismember on the first three columns of those dates (or pick whatever units you want to compare).

The output will be your desired logical vector.

Here is a full working example:

% all dates:
beg_date = datenum('18-Apr-2015 16:30:00');
end_date = datenum('19-Apr-2015 10:00:00');
interval = 10/24/60;
dat_mat = datevec(beg_date:interval:end_date);
% fake data:
holidays = {'18-Apr-2015 16:50:00';'19-Apr-2015 09:50:00'};
hol_mat = datevec(holidays)
% number of columns of the datevectors to compare:
N = 5;
idx = ismember(dat_mat(:,1:N),hol_mat(:,1:N),'rows');

and the output is a logical vector, exactly as requested. For this example it is true here:

>> find(idx)
ans =
     3
   105

This method has the advantage that you can very simply change the units that are being compared (by changing N), without needing to change any format strings.

[Ekaterina Serikova]

Dear Stephen, I have a strange thing happening with the dates when I convert them from "cell" to "datevec" - not the same dates are in datevec, please find attached. What can be a problem? I use

hol=datevec(holidays);

[Stephen23]

@Ekaterina Serikova: don't worry, this is not your fault! This is because in the USA we use a an illogical date format that no one else in the world uses: mm/dd/yyyy. And lots of US software uses this as the default. Because the rest of the word generally use a sequence that progresses in order (dd/mm/yyyy or yyyy/mm/dd) you can imagine the confusion that this can cause!

The solution is simple: you should always specify the date format when converting the date:

vec = datevec(in_strings,'dd/mm/yyyy')

An even better solution is to always use an ISO 8601 date format for all date strings: these are always unambiguous and have the advantage that they sort simply into chronological order!

Answer 2

[Azzi Abdelmalek]

You can use the ismember function

[Ekaterina Serikova]

Dear Azzi, ismember does not work, I think because the data formats are different: the larger vector is 106 by 1 char vector (dd-mmm-yyyy hh:mm:ss) and the other is 44 by 1 cell vector (dd.mm.yyyy). How can I make them equal and use ismember?

[Azzi Abdelmalek]

Ok, post a sample of your data

[Azzi Abdelmalek]

[~,a]=xlsread('holidays.xlsx');
b={'03-Apr-2015 16:30:00','06-Apr-2015 16:30:00','07-Apr-2015 16:30:00','08-Apr-2015 16:30:00'},;
bb=datestr(b,'dd.mm.yyyy'),
out=ismember(a,bb)