SOLVED

COUNTIFS between hours

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3166755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECOUNTIFS%20between%20hours%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3166755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EGood%20morning%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20count%20the%20entries%20that%20fall%20within%20a%20certain%20time%20range%20(22%3A00%20to%204%3A00)%2C%20and%20I'm%20using%20countifs%2C%20but%20I%20keep%20getting%200%20instead%20of%201%20even%20when%20the%20entry%20is%20within%20that%20range.%20I've%20tried%20using%2024%20and%2012%20hour%20formats%20and%20always%20get%200.%20Why%20could%20this%20be%20happening%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348273i62145BB7530E426A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Ber93_0-1644919013151.png%22%20alt%3D%22%5C%26quot%3BBer93_0-1644919013151.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3166755%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
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?

Ber93_0-1644919013151.png

 

 

2 Replies
best response confirmed by Ber93 (Occasional Contributor)
Solution

@Ber93 

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)