formula attempting to confirm (true or false) if 3 logic conditions are met

Karl E

New Member
Joined
Apr 3, 2015
Messages
12
Greetings All:



I came up with a formula attempting to confirm (true or false) if 3 logic conditions are met.

I'm attempting to determine if any person being tracked on my spreadsheet entered the facility and was physically here between say 11:00 and 11:10 on a specific date. I have a calendar on a different tab where I'm attempting to utilize this formula.



On the spreadsheet I'm using the tab I track peoples coming and goings on is called "Usage". Entry to the facility is entered in H Column, Exit time is entered in I Column and Date is entered in the E Column.



Someone could come in at 10:00 and been here until 12:00. The end result is, it would still count as true because they were actually here between the hours of 11:00 and 11:10 on that specific date. So as long as all three of the conditions are all true (person here between 11 and 11:10 on a specific date) the calendar is formatted to turn green in that time slot.



There are several attempts at formulas I've come up with but I keep getting the Value Error



=AND(IF(Usage!H4000:H4999>=TIME(11,30,0),IF(Usage!I4000:I4999<=TIME(11,40,0),IF(Usage!E4000:E4999=DATE(2018,4,24),1,0)))) get Value error

=AND((Usage!H4000:H4999>=TIME(11,30,0)),(Usage!I4000:I4999<=TIME(11,40,0)),(Usage!E4000:E4999=DATE(2018,4,24)),1,0) Get Value Error<o:p></o:p>

<o:p></o:p>

=AND((Usage!H4000:H4999>=TIME(11,30,0)),(Usage!I4000:I4999<=TIME(11,40,0)),(Usage!E4000:E4999=DATEVALUE("4/24/2018")),1,0) Get Value Error<o:p></o:p>



I hope this clear. If you need further details please let me know. Thank you for any help in advance.



Sincerely,



Karl Stamm
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Maybe:

=AND(Usage!H4000:H4999>=TIME(11,30,0),Usage!I4000:I4999<=TIME(11,40,0),Usage!E4000:E4999=DATE(2018,4,24))
 
Upvote 0
Thank you for your reply. It makes good sense and I tried it; but unfortunately I get the same Value Error. I'm stumped. Thank you so much for you time and taking a look at it though.

Sincerely,

K
 
Upvote 0
Maybe:
Excel Workbook
EFGHIJ
1DateInOut
24/24/201810:00 AM12:45 PMTRUE
34/24/201811:10 AM1:00 PMFALSE
44/24/201810:00 AM1:00 PMTRUE
54/24/201810:00 AM11:30 AMFALSE
64/25/201810:00 AM11:30 AMFALSE
Sheet
 
Upvote 0
If you're trying to see if anyone on the list is on site during the interval you select, try this array formula:


ABCDEFGHIJ
startenddateCount
Ann1-Jan
Bob
Crystal
Dan
Erica
4005Fred
Ann
Bob
Crystal
Dan
Erica
Fred
4012Ann
Bob
Crystal
Dan
Erica
Fred

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]11:40[/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4001[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4002[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4003[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4004[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]23:30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4006[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4007[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4008[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4009[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]10:15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4010[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4011[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4013[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4014[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4015[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4016[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:35[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4017[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Usage

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas
Cell

<tbody>
[TH="align: left"]Formula[/TH]

[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=SUM( IF(D2=Usage!$E$4000:$E$4017,IF(IF(C2< Usage!$I$4000:$I$4017,C2,Usage!$I$4000:$I$4017)-IF(B2> Usage!$H$4000:$H$4017,B2,Usage!$H$4000:$H$4017)>0,1)) )}[/TD]

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Same idea as AhoyNC, but it checks every row in the range and returns a count of how many rows match the criteria.
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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