+ Reply to Thread
Results 1 to 8 of 8

Need a COUNT on occurrences per day

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Need a COUNT on occurrences per day

    I've done some research, but am running out of time on getting this done.
    We have a report that is ran every two weeks. This report shows the date and time in column A that an employee swipes his/her badge at a specific terminal. I need to be able to do a count total for each day, which seemed pretty simple by itself.

    The caveat here is, They don't want me to modify the report any (as in get rid of the time...hence I can't use the actual date in a lookup), and it has to be a function that can be copied and then dropped in to the next report in two weeks and have it work without any modification.

    a sample:
    Event Date Event/Point Description Logical Device Last Name First Name Employee ID EDR COST
    1/11/2013 12:36 PM Access Granted KSC-E167 EDR Turnstile F DIANA 200000 0
    1/10/2013 11:38 AM Access Granted KSC-E167 EDR Turnstile F DIANA 200000 0
    1/17/2013 12:40 PM Access Granted KSC-E167 EDR Turnstile F DIANA 200000 0
    1/17/2013 12:40 PM Access Granted KSC-E167 EDR Gate F DIANA 200000 0
    1/22/2013 12:04 PM Access Granted KSC-E167 EDR Turnstile F DIANA 200000 0
    1/15/2013 11:55 AM Access Granted KSC-E167 EDR Turnstile W Amanda 200001 3
    1/10/2013 11:59 AM Access Granted KSC-E167 EDR Turnstile D Mike 200005 0
    1/22/2013 12:07 PM Access Granted KSC-E167 EDR Turnstile D Mike 200005 0
    1/18/2013 6:22 PM Access Granted KSC-E167 EDR Turnstile D Mike 200005 0
    1/12/2013 6:10 PM Access Granted KSC-E167 EDR Turnstile D Mike 200005 0

    What I need is a total as follows

    1/10/2013 - 2
    1/11/2013 - 1
    1/12/2013 - 1

    and so on.

    Any ideas?

    pulling my hair out.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: Need a COUNT on occurrences per day

    Hi and welcome to the forum

    This is 1 of those times when a copy/paste into here ends up with your data being hard to seperate

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a COUNT on occurrences per day

    AHA! Thanks for the welcome, and will do in just a few.

  4. #4
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a COUNT on occurrences per day

    EDR After.xlsxEDR Before.xlsx

    The count should represent the number of times that an entry showed up from 12:00:01 to 11:59:59 of each day.
    I was going to do a function for each count doing a lookup on the date cell but it saw the time too.

    ...but then my train of thought went out the door when I was told I need to carry it over each two weeks...

    I hope the results are easy to determine from what I posted. I know I'm obviously trying to make this harder than it really is.
    Last edited by graymatterz; 02-21-2014 at 06:37 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: Need a COUNT on occurrences per day

    Im not sure where you get 85 from, maybe you are including something in the count that I missed?

    I used this, copied down, and got 188...
    =COUNTIFS($A$8:$A$2598,">="&I8,$A$8:$A$2598,"<"&I8+1)

  6. #6
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a COUNT on occurrences per day

    It was a random number cause I couldn't get a straight count...sorry. Just made it up (i guess I should have pointed that out)

  7. #7
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a COUNT on occurrences per day

    OK...i physically counted them and there are 181 entries for that day, but it's coming up 188. I assume it is still seeing the time somehow and including extras?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: Need a COUNT on occurrences per day

    aahh OK, I see what the prolem is. I assumed that I8 was just a puere date, but that too has time attached.

    Change to this...
    =COUNTIFS($A$8:$A$2598,">="&INT(I8),$A$8:$A$2598,"<"&INT(I8)+1)

  9. #9
    Registered User
    Join Date
    02-21-2014
    Location
    KS
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need a COUNT on occurrences per day

    Thank you. I was pulling my hair out here. I'm not an excel guru for sure and was handed this project.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  2. [SOLVED] Count Word Occurrences from a different tab
    By Jaypelt in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-04-2013, 02:48 PM
  3. Count number of occurrences in a day
    By quinnbri in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2013, 05:49 PM
  4. count Re-occurrences
    By stevekirk in forum Excel General
    Replies: 15
    Last Post: 10-16-2006, 02:46 AM
  5. Count unique occurrences of name
    By jhicsupt in forum Excel General
    Replies: 4
    Last Post: 10-05-2005, 01:05 PM

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