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.

C	N	O
2/8/2018 04:00	2/7/2018	1
2/8/2018 07:30	2/8/2018	3
2/8/2018 13:45	2/9/2018	1
2/9/2018 02:15	2/10/2018
2/9/2018 10:35	2/11/2018

<tbody>
</tbody>

 

[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.