+ Reply to Thread
Results 1 to 27 of 27

"Head Count" of time periods crossing midnight

  1. #1
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Question "Head Count" of time periods crossing midnight

    Hi there!

    I need a littlee help with excel as I was wondering if my quest was even possible:

    Desired Solution:
    I want to count the amount of times, a timeframe exists.
    Confusing - i Know. thats why I will supply my excel to make sense.

    There are two columns counting sleeping time:

    Starttime End time
    08:15 10:30
    12:20 13:45
    17:00 18:00
    ... ...

    At the end I want the following solution:
    He is sleeping X-times at:

    00:01 - 01:00 8 Times
    01:01 - 02:00 6 Times
    ...
    ...
    ...
    23:01 - 00:00 7 Times

    so far I can count how many times he fell asleep between a certain timeframe ("Fell 8 times asleep between 17:01 - 18:00) and also how many times he woke up between a certain time.
    What I can not do is count the times in between and i was wondering if anyone has an Idea how to handle this?

    BR
    hchamala
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: need help to Count amount of areas of numbers

    Quote Originally Posted by hchamala View Post
    What I can not do is count the times in between and
    in between what? in your file, col ''N'' has the count of the times from the beg col and end col where the times are greater than or less than the spec'd cell.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: need help to Count amount of areas of numbers

    Is this what you had in mind?

    =SUMPRODUCT(((C$2:C$100<=K3)*(D$2:D$100>I3)+((C$2:C$100<=K3)+(D$2:D$100>I3))*(D$2:D$100<C$2:C$100))*(C$2:C$100<>""))
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: need help to Count amount of areas of numbers

    OMG this seems to be exactly what i wanted! Let me double check and come back to you :-)
    Thanks so far!

  5. #5
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: need help to Count amount of areas of numbers

    Quote Originally Posted by Glenn Kennedy View Post
    Is this what you had in mind?

    =SUMPRODUCT(((C$2:C$100<=K3)*(D$2:D$100>I3)+((C$2:C$100<=K3)+(D$2:D$100>I3))*(D$2:D$100<C$2:C$100))*(C$2:C$100<>""))

    This works perfectly and does exactly what I wanted :-)
    However, when entering new data, excel is extremely slow and takes almost a minute to calculate.. any advice?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: need help to Count amount of areas of numbers

    You didn't say how big your dataset was. If it is quite big, it will take some time.

    So.. go for a coffee/smoke/walk... whatever floats your boat??

    if you don't want to wait, then maybe VBA? (I can't help with that)

    However, you're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  7. #7
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: need help to Count amount of areas of numbers

    well the dataset is increasing everyday and on average with around 6-7 entries/day...
    maybe Ill look into the VBA solution as well.

    but your solution is awesome! thanks!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: need help to Count amount of areas of numbers

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  9. #9
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: need help to Count amount of areas of numbers

    Will do. But for now I think I need some additional help with the Macro. Maybe we can move this topic to VBA section?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: "Head Count" of time periods crossing midnight

    Done. I have also edited the title to reflect the exact nature of your underlying problem. Most often this sort of formula is used by people wanting a head count of how many staff are on duty during the hours of the day. A pretty close analaogy to your situation.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    Here is the for macro. Verified Ok.Macro result in Column O.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: "Head Count" of time periods crossing midnight

    Kvsrinivasamurthy: thanks for picking this one up! I can't handle VBA at all....

    hchamala: you didn't really answer my Q. Out of interest, how many rows of data have you? What EXACTLY is the formula that you have been using?

  13. #13
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    Quote Originally Posted by Glenn Kennedy View Post

    hchamala: you didn't really answer my Q. Out of interest, how many rows of data have you? What EXACTLY is the formula that you have been using?
    Currently I have around 150 Datarows. But it increased daily by around 6-7 entries.
    the exactly formular I am using is:

    ((can not upload the formular - website blocks it and says "access denied"))

    ... and I know, checking every column completely (C:C) is overkill.. might reduce it to a smaller number (e.g. C2:C1000) but so far I wanted to see, If a VBA might be able to handle such task more flawlessly


    @Kvsrinivasamurthy: THANKS for the Macro! Iam bound to check it now and will come back to you :-)

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: "Head Count" of time periods crossing midnight

    Not only is it overkill... it is mad. SUMPRODUCT will evaluate 1,000,000 rows. The best approach is to use a Named Range to adjust the column ranges correctly. Do you know how to do that.

    if you post formulae with > or < it works if you put a space after the symbol.

  15. #15
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    Quote Originally Posted by Glenn Kennedy View Post
    Not only is it overkill... it is mad. SUMPRODUCT will evaluate 1,000,000 rows. The best approach is to use a Named Range to adjust the column ranges correctly. Do you know how to do that.

    if you post formulae with > or < it works if you put a space after the symbol.

    = SUMMENPRODUKT(((Datensatz!C:C < = A2)*(Datensatz!D:D > A3)+((Datensatz!C:C < = A2)+(Datensatz!D:D > A3))*(Datensatz!D:D < Datensatz!C:C))*(Datensatz!C:C < > ""))

    but as we already talked, this is overkill. Currently I am working on the VBA solution.



    Quote Originally Posted by kvsrinivasamurthy View Post
    Here is the for macro. Verified Ok.Macro result in Column O.
    Please Login or Register  to view this content.
    I checked the Code and it is really great! does the correct and hoped job :-)
    Only one adjustment I am still trying to figure out: If I wanted the checked timeframes to be smaller (not count how many time within one hour, but rather within 10 minute frames) - how do I change the code to do this?

    I tried adjusting "Time2 = WorksheetFunction.Ceiling(Range("D" & TR) * 24, 1) - 1" to "Time2 = WorksheetFunction.Ceiling(Range("D" & TR) * 24, 0.5) - 1" to check in half hour frames but dont think it is right. Any advice?

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: "Head Count" of time periods crossing midnight

    Shout if you want to use formula/Named Range approach to adjust the column ranges automatically. With only a few 00 rows, calculation will be instantaneous.

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    Only one adjustment I am still trying to figure out: If I wanted the checked timeframes to be smaller (not count how many time within one hour, but rather within 10 minute frames) - how do I change the code to do this?
    Pl upload a sample file showing how exactly you want the result just like previous file

  18. #18
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    Here it is.

    The difference to the solution before is:

    - counter counts amount of times within a 5 minutes frame (and not within hour slots)


    I have also provided a graphical solution / vizualisation to make it more easy to understand

    As I went through your code, I think i figured, that counts(Tcount) can only provide the counted number in regards to an integer (e.g. counts(0), counts(1),... counts(23))
    How ever, I want it to count how many times were between 12:30 and 12:35 for example. therefore I'd need a "counts(12,5)"

    I am happy for any input.
    Thanks
    Attached Files Attached Files

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: "Head Count" of time periods crossing midnight

    RU talking to me or to KVS...?

  20. #20
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    Quote Originally Posted by Glenn Kennedy View Post
    RU talking to me or to KVS...?
    my last message was in regards to kvsrinivasamurthy

  21. #21
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    The example have time ranges in one day. Will there be no time ranges from one day to next day.
    Eg:21:00 hrs today to 2:00 hrs next day.
    Last edited by kvsrinivasamurthy; 12-09-2020 at 01:00 PM.

  22. #22
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21
    Quote Originally Posted by kvsrinivasamurthy View Post
    The example have time ranges in one day. Will there be no time ranges from one day to next day.
    Eg:21:00 hrs today to 2:00 hrs next day.
    I know, this example doesn't have dates over midnight.
    In the original dataset there will be ones though.

    How ever it is not the major concern here. The problem here is, that I want the Macro to count within smaller time-frames: don't count how often it exists between 01:00-02:00 but rather count how many times it exists between 01:00-01:05

  23. #23
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    Here is the code. It works even if you have overnight time ranges.
    Pl see file. See row numbers 267 and 292 notes.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    Quote Originally Posted by kvsrinivasamurthy View Post
    Here is the code. It works even if you have overnight time ranges.
    Pl see file. See row numbers 267 and 292 notes.
    Please Login or Register  to view this content.
    Looks promising!
    What exactly do you mean with your notes in row 267 and 292? Why are these the only two results that are not correct?

  25. #25
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    In 292 time is 23:55.In the table that is the end time, where time ends. The 292 slot is from 23:55 to 24:00. This slot is not occupied as per table. This is my understanding. I don't know how you have taken as 1 (occupied)
    The slot i feel should be
    0-4
    5-9
    10 - 14
    15-19

    If 5 shown in both the slots where % should be put.
    This is my view.

  26. #26
    Registered User
    Join Date
    09-23-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: "Head Count" of time periods crossing midnight

    i just got it - yes, my manual calculation was off: I counted the time between 23:55 to 00:00 as well but the table stoped at 23:55.

    So I guess this is the final solution! Thank you soooo much!!

  27. #27
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,999

    Re: "Head Count" of time periods crossing midnight

    Thanks for feedback. Pl mark the thread solved.

+ 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] See if timestamp is within two time values crossing midnight
    By klf in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2019, 04:55 PM
  2. [SOLVED] Userform Time crossing Midnight not working
    By demarc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-17-2019, 10:25 AM
  3. Replies: 1
    Last Post: 02-24-2019, 10:07 AM
  4. [SOLVED] Tiimelog - Time Difference Crossing Over or Past Midnight
    By mycon73 in forum Excel General
    Replies: 6
    Last Post: 10-20-2016, 06:41 AM
  5. Time crossing midnight
    By belle08 in forum Excel General
    Replies: 4
    Last Post: 01-27-2014, 03:47 PM
  6. Replies: 13
    Last Post: 11-04-2013, 02:30 AM
  7. Replies: 3
    Last Post: 07-26-2012, 10:50 AM

Tags for this Thread

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