Caclulating calls taken on only Sundays of each month

[snedman]

I am at a call center and need to calculate how many calls that we are taking on Sundays.

the dates are in the format 5/01/02 in B7:B200 there are of course 4 Sundays in May 5/5/02
5/12/02
5/19/02
5/26/02

Is there a way to count the number of Sundays in the B column, so that it counts all instances of sunday dates of:
5/5/02
5/12/02
5/19/02
5/26/02

Thank you all very much in advance.

Snedman

[IML]

assuming a range of a1:b5, with dates in A and calls in B, you could use

=SUMPRODUCT((WEEKDAY(A1:A5)=1)*(B1:B5))

good luck

[snedman]

I just want to count the actual dates for Sunday. That would be the easiest way.

the dates are B5:B200. I just want it to count all of the times in Columb B that it find the dates of:
5/5/02
5/12/02
5/19/02
5/26/02
in total so that if it sees the date 5/26/02 5 times, 5/12/02 5 times, and 5/5/02 4 times it would say it found those instances 14 times. Thank you for your patience

[Aladin Akyurek]

On 2002-05-03 08:02, snedman wrote:
I just want to count the actual dates for Sunday. That would be the easiest way.

the dates are B5:B200. I just want it to count all of the times in Columb B that it find the dates of:
5/5/02
5/12/02
5/19/02
5/26/02
in total so that if it sees the date 5/26/02 5 times, 5/12/02 5 times, and 5/5/02 4 times it would say it found those instances 14 times. Thank you for your patience

=SUMPRODUCT((WEEKDAY(B5:B200)=1)+0)

[IML]

edit - I total misunderstood, this would appear to work if...

If you want to find out how many sundays in a month given a date, give

=INT(((DATE(YEAR(A1),MONTH(A1)+1,1))-(DATE(YEAR(A1),MONTH(A1),1)+7-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))))/7)+1

a try. A1 is a date. Not fully tested, but it appears to work. Good luck.

[ This Message was edited by: IML on 2002-05-03 08:21 ]

[snedman]

Awesome, It works, it works. You are all awesome Thank You, Thank You

Snedman

[g_erhard]

On 2002-05-03 08:02, snedman wrote:
I just want to count the actual dates for Sunday. That would be the easiest way.

the dates are B5:B200. I just want it to count all of the times in Columb B that it find the dates of:
5/5/02
5/12/02
5/19/02
5/26/02
in total so that if it sees the date 5/26/02 5 times, 5/12/02 5 times, and 5/5/02 4 times it would say it found those instances 14 times. Thank you for your patience

(I'm not going to post a formula; two have already been posted that work fine.)

Breakdown of Problem:
Given a range of datestamps for calls taken, determine how many occurred on Sundays.

Solution:
Excel has the ability (using the Weekday function) to tell you what day of the week a given date falls on.

Weekday(A1)

Now, the result is a NUMBER. 1 for Sundays, 2 for Mondays, 3 for Tuesdays, etc. That gives us the next part of the problem, our logical test.

Weekday(A1)=1 | Sunday = TRUE | Any other day = FALSE

So, if you set up a formula (or multiple formulas) to examine each date in your range, and only 'count' those that fall on Sundays, you should get your answer.

Now, if you KNOW BEFOREHAND which dates specifically fall on Sunday, then you can write formulae to see if the date falls within a given set, and go from there. But the Weekday solution will not limit you to a predefined date range; the formula will work without rewriting even if you run the same report for the next month, or year, or decade.