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>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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")
 
Upvote 0
You are welcome.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top