# "Head Count" of time periods crossing midnight

1. ## "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

2. ## Re: need help to Count amount of areas of numbers

Originally Posted by hchamala
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. ## 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<>""))

4. ## 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. ## Re: need help to Count amount of areas of numbers

Originally Posted by Glenn Kennedy
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. ## 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.

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. ## 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. ## Re: need help to Count amount of areas of numbers

You're welcome.

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. ## 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. ## 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. ## 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.``

12. ## 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. ## Re: "Head Count" of time periods crossing midnight

Originally Posted by Glenn Kennedy

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:

... 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. ## 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. ## Re: "Head Count" of time periods crossing midnight

Originally Posted by Glenn Kennedy
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.

Originally Posted by kvsrinivasamurthy
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. ## 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. ## 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. ## 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

19. ## Re: "Head Count" of time periods crossing midnight

RU talking to me or to KVS...?

20. ## Re: "Head Count" of time periods crossing midnight

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

21. ## 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.

22. Originally Posted by kvsrinivasamurthy
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. ## 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.``

24. ## Re: "Head Count" of time periods crossing midnight

Originally Posted by kvsrinivasamurthy
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. ## 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. ## 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. ## Re: "Head Count" of time periods crossing midnight

Thanks for feedback. Pl mark the thread solved.

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