Lookup/IF on Times with Wildcards

[Mango1980]

is it possible?

Example data below. Basically, I want to build a formula that says on x date (01/02/2018), is there a value during the 7:00/8:00/9:00 hour? I've tried wildcards and lookups, but I think the format of the time is the biggest issue. I can get results if I enter as '7:32 vs 7:32 AM but I'm working with over 3000 lines and really don't want to manually enter all of the times. HELP!

[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1/2/2018 7:32[/TD]
[/TR]
[TR]
[TD]1/2/2018 7:41[/TD]
[/TR]
[TR]
[TD]1/2/2018 7:52[/TD]
[/TR]
[TR]
[TD]1/2/2018 7:53[/TD]
[/TR]
[TR]
[TD]1/2/2018 8:16[/TD]
[/TR]
[TR]
[TD]1/2/2018 8:29[/TD]
[/TR]
[TR]
[TD]1/2/2018 8:33[/TD]
[/TR]
[TR]
[TD]1/2/2018 8:34[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Are you entries valid date/time entries, or are they entered as Text?

If they are valid date/time entries, you can take advantage of how Excel stores date/times. It store them as the number of days since 1/0/1900, and time is just the fractional component of one day. So 9:00 AM is equal to 9/24.

So, you can get the time portion of a date/time field by removing the decimal, like this (for an entry in cell A1):
=A1-INT(A1)

 

[Joe4]

Depending on what exactly you want to return, the following may be more useful.

Let's say that you have your various entries in the range A1:A5, and they are all valid time/date entries.
Now, let's say that you want everything in your date 7/8/9 hour, so you want everything greater than or equal to 7:00, but less than 10:00.
Let's enter those endpoints in two blank cells, H1, and I1.
In H1: 1/2/2018 7:00
In I1: 1/2/2018 10:00

Then you can just use a COUNTIFS formula to count how many entries in column A fall in that range, like this:

=COUNTIFS(A1:A5,">=" & H1,A1:A5,"<" & I1)

 

[Mango1980]

Thank you for your reply. The entries, unfortunately, are text: 1/2/2018 7:53:59 AM (one cell)
The data contains employee badge scans - I am trying to determine if there is at least one entry per hour. I am less concerned with the count/quantity and more concerned with whether there was a scan each hour. Does that help?

 

[Joe4]

OK, for an entry in cell A1, you can use this formula to get just the hour portion of the date/time:

=TEXT(TIMEVALUE(A1),"h")+0

Then you can use a COUNTIF to count the number of each value, i.e. to count the 7 o'clock hour entries if the formula above is in column B:

=COUNTIF(B1:B5,7)

Or, if you want a "Yes" or "No" to see if there were any in the 7 o'clock hour entries, simply use:

=IF(COUNTIF(B1:B5,7)>0,"Yes","No")

 

[Mango1980]

Fantastic! I'm going to give it a go! Thank you!

 

[Joe4]

You are welcome.