Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Count if between range of times

Copper Contributor

I have a spreadsheet with two sets of data. One has Overtime with start and end time and on the other tab I have a table to 30 minute intervals and I am trying for excel to tell me how many people I have working OT during that 30 minute interval. I can't figure it out. Please help

 

javoseabaugh_0-1704472942548.png

 

javoseabaugh_1-1704472981419.png

 

 

2 Replies

There is no set of people in your OP.  Impossible to answer.  Probable answer:  Loop thru people with BYROW, FILTERing the other tables to figure out what OT they are doing.

@javoseabaugh 

Perhaps

=SUMPRODUCT(
    ($N$3:$N$18 >=$P3) *
    ($N$3:$N$18 >=$Q3) *
    ($L$3:$L$18 <= $P3) *
    ($H$3:$H$18="Overtime")
)

 for

image.png