Count occurrences of dates in an excel file

2 views (last 30 days)
Zekeftw
Zekeftw on 23 Aug 2016
Edited: Andrei Bobrov on 23 Aug 2016
Hi,
The attached excel file contains a list of dates. I need help counting the number of occurrences for each date then sum them for each week. Where the week is counted from Sun - Sat.
For example, for the week of 5/1/15 John placed 21 orders.
Thanks

Answers (2)

Andrei Bobrov
Andrei Bobrov on 23 Aug 2016
Edited: Andrei Bobrov on 23 Aug 2016
[~,~,dc] = xlsread('Orders.xlsx'); % T = readtable('Orders.xlsx');
z = dc(2:end,:); % z = table2cell(T);
t = cellfun(@ischar,z); % t = ~cellfun(@isempty,z);
nd = weekday(datenum(z(t),'dd.mm.yy'));
ds = datenum(z(t),'dd.mm.yy');
ddf = diff([nd ds]);
lo = [true;ddf(:,1) < 0 | abs(ddf(:,2)) > 7];
i0 = double(t);
i0(t) = lo;
i1 = cumsum(i0).*t + 1;
i2 = ones(size(i1,1),1) * (1:3);
out = accumarray([i1(:),i2(:)],1);
out = out(2:end,:);

KSSV
KSSV on 23 Aug 2016
[num,txt,raw] = xlsread('Orders.xlsx') ;
dates = txt(:,1) ;
d = {'30-06-2016'} ;
% d = {'05-01-2015'} ;
idx = strcmp(dates,d) ;
count = length(find(idx==1));
  2 Comments
Peter Perkins
Peter Perkins on 23 Aug 2016
Your question is not quite clear to me, but you may find that converting your dates to datetime and using the week function is the way to go. Hope this helps.

Sign in to comment.

Categories

Find more on MATLAB Functions in Microsoft Excel 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!