"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

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.

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

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?

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!

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

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:

((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 :-)

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?

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

Originally Posted by Glenn Kennedy

RU talking to me or to KVS...?

my last message was in regards to kvsrinivasamurthy

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

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?

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!!