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

[Karl E]

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></o>

<o></o>

=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></o>



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



Sincerely,



Karl Stamm

 

[Ingolf]

Maybe:

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

 

[Karl E]

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

 

[AhoyNC]

Maybe:

Excel Workbook
	E	F	G	H	I	J
1	Date			In	Out
2	4/24/2018			10:00 AM	12:45 PM	TRUE
3	4/24/2018			11:10 AM	1:00 PM	FALSE
4	4/24/2018			10:00 AM	1:00 PM	TRUE
5	4/24/2018			10:00 AM	11:30 AM	FALSE
6	4/25/2018			10:00 AM	11:30 AM	FALSE
Sheet

 

[Eric W]

If you're trying to see if anyone on the list is on site during the interval you select, try this array formula:

	A	B	C	D	E	F	G	H	I	J
start	end	date	Count
Ann	1-Jan
Bob
Crystal
Dan
Erica
4005	Fred
Ann
Bob
Crystal
Dan
Erica
Fred
4012	Ann
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.