COUNTIFS formula.

[Peter h]

Hey guys. I have the hardest time with getting formulas to work the way I want. I've got a list of timestamps in Column "C", and a list of dates in Column "N" (starting in row 5, 1/1/2018,1/2/2018 etc.). In column "O" I want to count how many timestamps from column C fall on the date from column N. The tricky part is that my date rollover time is 6:00AM. So for instance if the timestamp in column C is "2/8/2018 04:00 AM" it should be counted as 2/7/2018. The formula I tried in column O is: =COUNTIFS(C:C,">="&Day(N5)+6/24,C:C,"<"&Day(N5+1)+6/24). All it's giving me is 0, and in my data it should be 4. What am I doing wrong here?

Here's just a small example of what it should look like.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]C
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]2/8/2018 04:00
[/TD]
[TD]2/7/2018
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2/8/2018 07:30
[/TD]
[TD]2/8/2018
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]2/8/2018 13:45
[/TD]
[TD]2/9/2018
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2/9/2018 02:15
[/TD]
[TD]2/10/2018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/9/2018 10:35
[/TD]
[TD]2/11/2018
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Re: Need a bit of help with this COUNTIFS formula.

Try:

=COUNTIFS(C:C,">="&N1+6/24,C:C,"<"&N1+30/24)

 

[Peter h]

Re: Need a bit of help with this COUNTIFS formula.

Try:

=COUNTIFS(C:C,">="&N1+6/24,C:C,"<"&N1+30/24)

Still calculating 0

Edit: Oh, nevermind. It did work. I didn't realize you took out the "DAY" function. Thank you so much.

 

[steve the fish]

Re: Need a bit of help with this COUNTIFS formula.

Then id imagine column C is text and not true dates. Highlight column C, press Data, Text to Columns and then Finish.

 

[Peter h]

Re: Need a bit of help with this COUNTIFS formula.

Then id imagine column C is text and not true dates. Highlight column C, press Data, Text to Columns and then Finish.

Edited my comment. It did work. Thank again, so much.