counting weekends

[bluestone]

Hi-
I have a spreadsheet with 1st column being numerical dates of the
year. Second column contains initials of person on call for the
corresponding date. Same person is on call for Friday, Saturday, and
Sunday (weekend on call). I am unsuccessfully trying to write VB code
to calculate how many weekends (not the total days) each person (by
initials) is on call. (Spreadsheet has several yrs of dates, and
people complaining that they have more weekends of call than someone
else)

TIA

CLS

 

[Bob Phillips]

Why use VBA.

Assuming dates in column A, people in column B

=SUMPRODUCT((WEEKDAY(A1:A1000)=6)*(B1:B1000="Steve"))

counts how many Fridays Steve has worked, so how many weekends.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Just count the Fridays.
=sumproduct(-($B$1:$B$2000="initials"),-(Weekday($A$1:$A$2000,1)=6)

You can replace "initials" with a cell that contains the person's initials
so you can copy the formula down next to a list of initials.