# Lookup/IF on Times with Wildcards

#### Mango1980

##### New Member
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!

 1/2/2018 7:32 1/2/2018 7:41 1/2/2018 7:52 1/2/2018 7:53 1/2/2018 8:16 1/2/2018 8:29 1/2/2018 8:33 1/2/2018 8:34

<colgroup><col></colgroup><tbody>
</tbody>

#### Joe4

##### MrExcel MVP, Junior Admin
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

##### MrExcel MVP, Junior Admin
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:
Code:
``=COUNTIFS(A1:A5,">=" & H1,A1:A5,"<" & I1)``

#### Mango1980

##### New Member
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

##### MrExcel MVP, Junior Admin
OK, for an entry in cell A1, you can use this formula to get just the hour portion of the date/time:
Code:
``=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:
Code:
``=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:
Code:
``=IF(COUNTIF(B1:B5,7)>0,"Yes","No")``

#### Mango1980

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

You are welcome.

1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...