Evaluate if an activity is happening during a given time interval

SecretProject5

New Member
Joined
Oct 12, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hello community, i have start and end times for events throughout a given day. I can calculate the length in time for each event and the amount of events, but I'm trying to evaluate if there was an active event in specific 5 minutes intervals.

For example:

2 events, the first one starts at 6:23pm and ends at 6:38pm, next event starts at 6:47pm and ends at 6:54. When evaluating the 5 minute intervals with this information, i'd like to get something like this:

6:20pm NO
6:25pm YES
6:30pm YES
6:35pm YES
6:40pm NO
6:45pm NO
6:50pm YES
6:55pm NO

I figured one way could be using an IF AND with "greater than or equal to" and "less than or equal to", but i was wondering if anyone had another idea. Thanks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the MrExcel forum!

Try:

Book1 (version 1).xlsb
ABCDE
1startend
26:20 PMYES6:23 PM6:38 PM
36:25 PMYES6:47 PM6:54 PM
46:30 PMYES
56:35 PMYES
66:40 PMNO
76:45 PMYES
86:50 PMYES
96:55 PMNO
107:00 PMNO
Sheet20
Cell Formulas
RangeFormula
B2:B10B2=IF(SUMPRODUCT(--(($E$2:$E$3>A3)*A3+($E$2:$E$3<=A3)*$E$2:$E$3-($D$2:$D$3<A2)*A2-($D$2:$D$3>=A2)*$D$2:$D$3>0)),"YES","NO")
 
Upvote 0
Solution
Hey! thank you very much for the suggestion. How come 6:20 and 6:45 show YES in the evaluation, when there's no activity during that specific time?

Perhaps i wasn't clear, the evaluation is a sort of snapshot on that specific point in time to see if there was any activity going on.
 
Upvote 0
I also got this suggestion from the excel subreddit
Excel Formula:
=IF(COUNTIFS($A$2:$A$3,"<="&D2,$B$2:$B$3,">="&D2),"YES","NO")

Excel Formula:
=IF(SUMPRODUCT((ROUND($A$2:$A$3/(1/24/60),0)*(1/24/60)<=MROUND(H2,"0:01"))*(ROUND($B$2:$B$3/(1/24/60),0)*(1/24/60)>=MROUND(H2,"0:01"))),"YES","NO")

OMPQuuy.png
 
Upvote 0
Hey! thank you very much for the suggestion. How come 6:20 and 6:45 show YES in the evaluation, when there's no activity during that specific time?

Perhaps i wasn't clear, the evaluation is a sort of snapshot on that specific point in time to see if there was any activity going on.
I was assuming that the 5 minute intervals started at the first row, and ended at the next row. For example, on row 2 it starts at 6:20 and ends at 6:25. The first event starts at 6:23 and ends at 6:38. So those 2 intervals overlap, from 6:23 to 6:25. That's why it says YES.

Incidentally, I can shorten my formula a bit if you don't mind array entered formulas.
 
Upvote 0
Like this:

Book1 (version 1).xlsb
ABCDE
1startend
26:20 PMYES6:23 PM6:38 PM
36:25 PMYES6:47 PM6:54 PM
46:30 PMYES
56:35 PMYES
66:40 PMNO
76:45 PMYES
86:50 PMYES
96:55 PMNO
107:00 PMNO
117:05 PMNO
127:10 PMNO
137:15 PMNO
Sheet20
Cell Formulas
RangeFormula
B2:B13B2=IF(SUM(--(IF($E$2:$E$3>A3,A3,$E$2:$E$3)-IF($D$2:$D$3<A2,A2,$D$2:$D$3)>0)),"YES","NO")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top