+ Reply to Thread
Results 1 to 14 of 14

Client Count Per Hour with Rounding Rules

  1. #1
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Client Count Per Hour with Rounding Rules

    I have an interesting problem regarding counting the number of clients in a building at a certain time. With some help from this forum, I was able to figure out how to get a count of the number of clients per hour based on their Time In and Time Out, but the issue I ran into is that the formula was only counting if someone was in for the full hour. For example, if someone arrived at 7:01am and left at 9:00am, the count would only show them as being present for the 8am time block.

    I need a way to revise this formula so that, even if a client is only present for one minute during an hour, they are counted. For example, if they arrive at 6:59am and leave at 8:01am, they would be marked present at 6am, 7am, and 8am.

    I have tried various permutations or ROUNDUP and ROUNDDOWN, neither of which are working. I am quite stuck.

    Attached is a sample spreadsheet showing the issue.

    Any help is appreciated. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Client Count Per Hour with Rounding Rules

    edit: ignore below -- requires use of FLOOR rather than MROUND -- will correct.

    perhaps below will work for you -- you don't provide expected results:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above would return: 1, 2, 2, 3, 3, 3, 3, 3, 3, 0, 1, 1
    (no entries for 4pm as end time of 4pm is presumed out of scope - as must be present for one minute of the hour)
    Last edited by XLent; 08-03-2021 at 09:42 AM.

  3. #3
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Holy cow. Yeah I never would have thought to use MROUND in this way. It works perfectly. There is an entry for 4pm, but it works as expected - if someone leaves at 4pm, they were still there, so they would still be marked as present.

    Is there a way to make this work only for certain dates? Say, for example, I make cell A1 a date-set date where a user enters a date, and then the formula only counts if the "Date" column in the table matches the date in cell A1. Does that make sense?

    Seriously, thank you so much for your help!

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Client Count Per Hour with Rounding Rules

    I think the below works - the previous one didn't (or at least, I don't think it did)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Is there a way to make this work only for certain dates? Say, for example, I make cell A1 a date-set date where a user enters a date, and then the formula only counts if the "Date" column in the table matches the date in cell A1
    Yes, you can add a further test in the SUMPRODUCT that checks the value of date column versus criteria

  5. #5
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Client Count Per Hour with Rounding Rules

    Try this formula and see if it does the trick:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you want people who were there when the hour shifted so the one who punched out at 4:00 counts for 4 then change the number to +.041667
    Last edited by DavidBowman; 08-03-2021 at 10:07 AM.
    If I was able to help you, you can thank me by clicking the Add Reputation button below and marking the thread Solved.

  6. #6
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    I appreciate your response. Both of your methods worked for me perfectly!
    I am struggling on how to check the value in a date column. I keep trying to add an if statement to SUMPRODUCT, but I just get either 0 or #REF. I am assuming it should be added at the beginning. This is what I came up with, but it is not working:
    Please Login or Register  to view this content.
    My apologies; I am definitely a novice Excel user and have never used SUMPRODUCT before.

  7. #7
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Quote Originally Posted by DavidBowman View Post
    Try this formula and see if it does the trick:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you want people who were there when the hour shifted so the one who punched out at 4:00 counts for 4 then change the number to +.041667
    Hey thanks so much! This also worked perfectly, and I like the ability to change the rounding rules so easily. There are a lot of ways to approach the same issue within Excel.

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Client Count Per Hour with Rounding Rules

    so, adapting the earlier example

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the other COUNTIFS approach provided:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Quote Originally Posted by XLent View Post
    so, adapting the earlier example

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the other COUNTIFS approach provided:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks again for your help! So, I am getting some unexpected behavior. I uploaded an updated sample spreadsheet to better explain the issue.

    Let's say I only have one person on one date, and they arrive at 1pm and leave at 6pm. I would expect to see 1 client in column U from 1pm to 6pm. Instead, I see the following:
    1,2,0,0,0,0,0,2,1,2,1,4,0

    None of these values should be above a 1, and there should be 0's from 6am through 12pm. Perhaps the formula doesn't recognize the difference between AM and PM?

    The second example just game me an error so I wasn't able to see if it worked.

    Again, I really appreciate your patience and time!

    Edit: I got the second formula working better than the first, to an extent. The issue with the second one is that it shows a maximum of 1 clients, almost as though it is giving a binary TRUE/FALSE result instead of an actual count of the number of clients. Weird!
    Attached Files Attached Files
    Last edited by applehugger; 08-03-2021 at 12:40 PM.

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Client Count Per Hour with Rounding Rules

    my bad.... in this case you need to split this test from the AND/OR tests... so:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    so, ignoring the date test piece for a second...
    the original calculation identifies rows where entire hour worked then adds any row where either start/end occur within the hour... i.e. = x + y
    my initial modification simply did = z * x + y (where z is date test), hence you got unexpected results; the above modification is, in effect, doing = z * (x + y)
    Last edited by XLent; 08-03-2021 at 01:02 PM. Reason: reworded

  11. #11
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Quote Originally Posted by XLent View Post
    my bad.... in this case you need to split this test from the AND/OR tests... so:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    so, ignoring the date test piece for a second...
    the original calculation identifies rows where entire hour worked then adds any row where either start/end occur within the hour... i.e. = x + y
    my initial modification simply did = z * x + y (where z is date test), hence you got unexpected results; the above modification is, in effect, doing = z * (x + y)
    AWESOME! This works perfectly! Thank you for explaining the error and how you corrected it; that sounds like a mistake I would make as well, as math is not and has never been my strong suit.
    Well, this is wonderful; many thanks again for all of your help, patience, and persistence!

  12. #12
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Hi there! After using this formula for over a month now, we noticed an unusual anomaly with XLent's solution, which I suspect is due to the way I explained the way that we need the rounding to work.

    Any client who comes in or leaves exactly on the hour is counted twice, so if we have one client who shows up at 08:00:00 AM, it counts two clients present for the 8 AM hour. To work around this, we have been offsetting any client who comes in or leaves on the hour by one second (so 08:00:01, for example).

    Is there a way to modify this formula so that it only counts one client as present if they come in or leave exactly on the hour?

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Client Count Per Hour with Rounding Rules

    I can't quite remember the specifics in play here but suspect the previous approach was somewhat over complicated -- issue arising from the 0&value and FLOOR, e.g

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the 0& was prefixed to handle blanks whilst avoiding array-entry requirement (c/o IF)

    the above coupled with existing calc logic:
    the original calculation identifies rows where entire hour worked then adds any row where either start/end occur within the hour... i.e. = x + y
    means the record is added twice over, once within the "x" section and then, again, within the "y" section (referring to earlier post)

    so, referring back to over-complication, if start/end on the hour are included -- i.e. a start at 08:00 and end at 12:00 would result in being included for hours 8-12 inclusive, then perhaps below will suffice?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    01-16-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    171

    Re: Client Count Per Hour with Rounding Rules

    Quote Originally Posted by XLent View Post
    I can't quite remember the specifics in play here but suspect the previous approach was somewhat over complicated -- issue arising from the 0&value and FLOOR, e.g

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the 0& was prefixed to handle blanks whilst avoiding array-entry requirement (c/o IF)

    the above coupled with existing calc logic:means the record is added twice over, once within the "x" section and then, again, within the "y" section (referring to earlier post)

    so, referring back to over-complication, if start/end on the hour are included -- i.e. a start at 08:00 and end at 12:00 would result in being included for hours 8-12 inclusive, then perhaps below will suffice?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thank you so much XLent! This works perfectly and counts the clients exactly as it needs to. I'm so grateful that you returned to this topic over a month after posting your initial solution. Your explanation is also very interesting; it's nice to know why the previous formula was behaving as it did.

+ 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] Check to See Client Count during Every Hour
    By applehugger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2021, 07:24 AM
  2. Replies: 4
    Last Post: 01-24-2018, 06:09 PM
  3. [SOLVED] Change Rounding Formula to fit different rules
    By sabin348 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2017, 08:48 AM
  4. [SOLVED] VBA Not Following Consistent Rounding Rules?
    By redtop91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2014, 05:25 PM
  5. [SOLVED] How can I alter the rounding rules?
    By Greg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2006, 01:40 AM
  6. [SOLVED] Modify Rounding Rules
    By NickDangr in forum Excel General
    Replies: 12
    Last Post: 02-09-2005, 03:06 PM
  7. How do I modify rounding rules in Excel?
    By merritaf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2005, 06:06 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