COUNTIFS between hours

Occasional Contributor

Good morning


I'm trying to count the entries that fall within a certain time range (22:00 to 4:00), and I'm using countifs, but I keep getting 0 instead of 1 even when the entry is within that range. I've tried using 24 and 12 hour formats and always get 0. Why could this be happening?




2 Replies
best response confirmed by Ber93 (Occasional Contributor)


Actually you count events in periods 22:00-24:00 AND 00:00-04:00, they don't intersect, thus result is always 0.

You need to indicate somehow which time is from next date, i.e. add one date or so.

Thank you so much. I solved it by nesting OR inside an IF and using TIME:
=IF(OR([@[Hour of Created]]>=TIME(22,0,0),[@[Hour of Created]]<TIME(4,0,0)),1,0)