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 the time-period say 11:00 to 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 use to I track peoples comings and goings the tab is labeled “Usage”. Entry to the facility is entered in column H, exit time is entered in column I. and date is entered in column E

Someone could come in at 10:00; be here until 12 noon and hopefully the formula would still return a value of true because the person was on the tracking sheet as being here on that specific date between the time period of 11:00-11:10. And if all 3 conditions are true the calendar would be formatted to turn that cell (the 11-11:10 Cell for that date) green.

There have been several attempts at formulas but keep getting Value error.” ]

After some time and thought I realized more descriptive information of the problem may help clarify what I'm attempting to do and what the actual problem is.

The calendar below is laid out in 10 min. increments from 7am to 16:00.
I wanted to create a formula that would look at the data in the table and be able to determine if an entry (on the data tracking sheet) in the start time and stop time column included one or more blocks of time (10 minute blocks) for a specific date. If there is a 10 minute block of time identified as having tracked someone being present at the facility on a specific date that cell would be formatted to turn green so that activity in the facility could be easily visually tracked.


So far I’ve had some assistance from the Mr. Excell forum and a couple of formulas developed from those ideas.

{=AND(IF(E1000:E1999=DATE(2018,1,17),IF(H1000:H1999)>TIME(11,0,0),-IF(I1000:I1999)0,1))} Returns value of FALSE

In my mind this formula says (if condition 1(date is 1-17-2018), condition 2(Time In is >11:00) and condition 3(Time out is < 11:20) all 3 conditions are true therefore the resulting value given would be 1. To follow up, the next step would be to format the cell to turn green if the value was 1.

The whole calendar could be populated with formulas for each individual date and time period to track activity based on data from the tracking sheet

Several other formulas have been developed but also failed.

{=AND((Usage!H1000:H1999>=TIME(11,0,0)),(Usage!I1000:I1999<=TIME(11,20,0)),(Usage!E1000:E1999=DATEVALUE("1/17/2018")),0,1)} Return value of FALSE

So far all my attempts to modify ideas offered in the Mr. Excell forum have failed. What I thought were logical formulas do not work as I thought they would.

Does anyone out there have any ideas. Any help would be Greatly Appreciated.

Respectfully,

Karl E

The following table is on a tab labled Usage:

E F G H I J
Date LibraryResource VLER TimeIn TimeOut E/T

1128 1/12/2018 Employee Computer 10:30 11:00 0:30:00
1129 1/12/2018 MHeV Assist y 12:00 12:10 0:10:00
1130 1/12/2018 internet 8:30 10:30 2:00:00
1131 1/12/2018 internet 8:30 9:30 1:00:00
1132 1/12/2018 MHeV Assist 9:00 9:10 0:10:00
1133 1/12/2018 E-Benefits Assist 10:00 10:20 0:20:00
1134 1/17/2018 internet 11:01 11:19 0:18:00
1135 1/17/2018 internet 11:00 11:30 0:30:00
1136 1/17/2018 Employee Computer 10:30 11:30 1:00:00
1137 1/17/2018 Employee Computer 10:30 11:30 1:00:00
1138 1/17/2018 MHeV Assist 10:00 10:10 0:10:00
1139 1/17/2018 MHeV Assist 10:00 10:05 0:05:00
1140 1/17/2018 MHeV Assist 10:00 10:15 0:15:00
1141 1/17/2018 MHeV Assist 10:15 10:30 0:15:00
1142 1/17/2018 MHeV Assist 10:30 10:35 0:05:00
1143 1/18/2018 Internet Personal Device 9:00 10:00 1:00:00
1144 1/18/2018 Employee Computer 9:30 10:00 0:30:00
1145 1/18/2018 internet 9:00 10:00 1:00:00



C D E F G H I J K L M N O P Q R S T U V AF AG AH
January
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 29 30 31
7:30
7:40
10:00
10:10
10:20
10:30
10:40
10:50
11:00
11:10
11:20
11:30
11:40
11:50
12:00
16:00