Problem counting times that occur between a fixed range that includes midnight

[idontcarewhateveriti]

I want to count how many parties partially overlap a fixed range that includes midnight. I know the start/stop times of the parties.

So for example, if a party starts at 7pm and goes until 1 am, it would fit my fixed range of 5pm-5am and be tallied.


I would ideally like to create a function that checks both times as conditions, and counts it once if at least one overlaps the range I'm interested in. Is this possible? I can also imagine a scenario where I look at the first list of start times, filter, and then check the second list of end times.

All the solutions I see floating around are for one time in between ranges, and rarely account for midnight.

[Alf]

Perhaps you could try using the mod function?

A formula like this “=MOD(stop_time-start_time,1) will always give the right time difference even if it passes midnight.

Alf

[Green Crocodile]

You can do this... Where column A has your start times and Column B has your end times. In any free column put the following formula and copy down.

Please Login or Register  to view this content.

[Green Crocodile]

If you would like to return a true or false value instead of a 0 or 1 you can remove the last "+0"

[idontcarewhateveriti]

I tested your formula on my first set of times, but it returned 'true' when the answer is false for the conditions I'm looking to test. I did fudge the times a bit on my post so I could hash it out on my own. The real range I'm looking at is 8 pm - 8 am.
a66fdbf6b6.png

[Green Crocodile]

I just put in your data set and tested it and it works fine for me. On your cells check to make sure the dates for both the start time and end time are the same. Even though it only shows the times excel might be storing it as a date and time. Change the cell format to date and time to check. If you like you can upload the workbook and I can work it out on yours.

Capture001.PNG

IF there are dates associated to the cell. You should be able to use this formula and it will work.

Please Login or Register  to view this content.

[Phuocam]

Try this ...

=OR(A1>20/24,A1<8/24,B1>20/24,B1<8/24)

[idontcarewhateveriti]

Oh man, this is data I previously narrowed down to events that occurred on the same day! It was originally input as a date as well as a time, but I formatted the cell to show time so I wouldn't confuse myself.

[Green Crocodile]

In that case this will work.

Please Login or Register  to view this content.

Phuocam has a awesome way of doing it too. One thing I would add to his formula (not even sure you will come across the scenario) is this...

=IF(B2 < A2,B2 < 20/24,OR(A2 > 20/24,A2 < 8/24,B2 > 20/24,B2 < 8/24))

Just in case they are party animals and party til the break of dawn!

Also if his doesn't work, I would definitely try putting MOD(cell,1) for every referenced cell. It seems like excel has dates connected to the times.

[idontcarewhateveriti]

In that case this will work.

Please Login or Register  to view this content.

Phuocam has a awesome way of doing it too. One thing I would add to his formula (not even sure you will come across the scenario) is this...

=IF(B2 < A2,B2 < 20/24,OR(A2 > 20/24,A2 < 8/24,B2 > 20/24,B2 < 8/24))

Just in case they are party animals and party til the break of dawn!

Also if his doesn't work, I would definitely try putting MOD(cell,1) for every referenced cell. It seems like excel has dates connected to the times.

This solution worked beautifully. Thank you so much for taking the time to answer.

[Green Crocodile]

You're welcome.