Lookup/IF on Times with Wildcards

Mango1980

New Member
Joined
Nov 5, 2018
Messages
3
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
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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
Joined
Nov 5, 2018
Messages
3
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
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
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")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top