+ Reply to Thread
Results 1 to 8 of 8

Counting the number of times a date appears in a list

  1. #1
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50

    Counting the number of times a date appears in a list

    I'm trying to assess how frequently an operating room is, and isn't, in use.

    I need to know:
    the number of days when there were 0 cases
    the number of days there was 1 case
    the number of days when there were 2 cases.
    As yet there are no days in which there were more than 2 cases but there might be in the future.

    I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.

    What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases. But there has to be a better way.
    Attached Files Attached Files
    Last edited by daddylonglegs; 11-25-2008 at 12:49 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    If you just want a number of each then you can do it like this.

    List start date in E1 and end date in E2 (on attached sheet I've calculated these automatically from the smallest and largest dates in the list but you can change these manually if you want)

    Now use this formula

    =SUM(IF(FREQUENCY(A$2:A$300,ROW(INDIRECT(E$1&":"&E$2-1)))=D5,1))

    Where D5 contains the occurence number

    That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

    See attached



    see attached
    Attached Files Attached Files

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    another way
    put a list of all the dates of the year in say col c starting at c1
    then in d1 =COUNTIF($A$2:$A$500,"="&C1)
    and drag down
    you can then see at a glance how many times any day was used
    then use
    =COUNTIF($D$1:$D$500,"=0")
    =COUNTIF($D$1:$D$500,"=1")
    =COUNTIF($D$1:$D$500,"=2")
    Last edited by martindwilson; 11-21-2008 at 08:25 PM.

  4. #4
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    199
    You can still summarize the data in a pivot table if that works for you. I took the table that you attached that had the date of service in column A, then in column B I put in the number 1 in each cell to correspond with each date. So, when you run a pivot off of both columns you can put the date in the date of service in the row field and then the cases in the data field. You will then have a date and a corresponding number of cases for that specific day.

    Hope that helps.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50
    Quote Originally Posted by daddylonglegs View Post
    If you just want a number of each then you can do it like this.

    List start date in E1 and end date in E2 (on attached sheet I've calculated these automatically from the smallest and largest dates in the list but you can change these manually if you want)

    Now use this formula

    =SUM(IF(FREQUENCY(A$2:A$300,ROW(INDIRECT(E$1&":"&E$2-1)))=D5,1))

    Where D5 contains the occurence number

    That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER

    See attached



    see attached
    Thanks for your response. It's an interesting solution. Unfortunately it doesn't appear to work. If you'll notice there are 86 dates in the list. But if you add up the dates in which there were one case per day (56) and dates on which two cases per day (15) it should add up to 86. But it only adds up to 71. So some cases are not being counted.

    Also, if you count the number of days between the start date and stop dates there are 227 days. But if you total all the days in the "occurances" column they add up to 228 days.
    Attached Files Attached Files

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Yes, there 86 dates in the list but not 86 different dates. As my formulas show 15 dates were shown twice,

    15*2+56*1 = 86

    There are 228 dates between 8th April and 21st November if you count both start date and end date, which you need to do as you have operations on those dates. The correct formula to count dates, including start and end, would be

    =F2-F1+1

    Otherwise if there was only 1 date on the list, for instance, then F2 would be the same as F1 and F2-F1 would give zero.

  7. #7
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50
    Quote Originally Posted by daddylonglegs View Post
    Yes, there 86 dates in the list but not 86 different dates. As my formulas show 15 dates were shown twice,

    15*2+56*1 = 86

    There are 228 dates between 8th April and 21st November if you count both start date and end date, which you need to do as you have operations on those dates. The correct formula to count dates, including start and end, would be

    =F2-F1+1

    Otherwise if there was only 1 date on the list, for instance, then F2 would be the same as F1 and F2-F1 would give zero.
    Sheesh! Why didn't I see that? Thanks very much. Mystery Solved.

    One more question. I know I'm supposed to label the thread as solved. The instructions say I need to:
    1) Click the EDIT button on your first post
    2) Click the GO ADVANCED button
    3) Select SOLVED from the Title dropdown (under the Reason for Editing box)
    4) Click the SAVE CHANGES button

    However, when I do this I can't find the Title dropdown mentioned in step 3.
    Last edited by Research RN; 11-25-2008 at 12:26 PM.

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    I marked it "SOLVED" for you. I agree it's not particularly easy to follow.....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1