Caclulating calls taken on only Sundays of each month

snedman

New Member
Joined
May 2, 2002
Messages
39
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top