I'm writing a formula that takes an average from a column based on several criteria. The last criteria I'm checking for is the time, and I've broken it into day (6:00 am-5:59 pm) and night (6:00 pm-5:59 am). I was unsuccessful in using the Hour() function in the formula itself, so I've used it in another column that returns an hour value based on the "time" column.

The criteria are mostly identical for the "day" and "night" averages, except for the "time" criteria. I need the "day" column to only average values in which the "time" value is greater than 5 and smaller than 18, and the night column to average those in which the "time" is less than 6 or greater than 17.

Here's the actual formula I'm using for "day":

=AVERAGEIFS(INDIRECT($P6&"!P:P"),INDIRECT($P6&"!E:E"),$Q6,INDIRECT($P6&"!S:S"),">5",INDIRECT($P6&"!S:S"),"<18")

I'm able to handle the AND portion by just separating it into two different criteria sets. But for the "night" portion I need an OR formula ("<6" or ">17").

Additionally, if it's possible to cut out the separate column for the Hour() that I mentioned above and work it directly into the final criteria, that'd be handy to know.

Thanks for your time.