Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Caclulating calls taken on only Sundays of each month

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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)


  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Snedman

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •