track 10 min blocks of time on a calendar based on a database where time in & time left ar

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

Thank you for your help Sir!

Yes it is very close to what I'm looking for. Your awesome!! I had to make a small modification
=SUMPRODUCT((Usage!$B$1000:$B$1150=Calendar!D$2)*(Usage!$D$1000:$D$1150<=Calendar!$C3)*(Usage!$E$1000:$E$1150>=Calendar!$C3+10/1440))>0 C3 had to be changed to C4
and the rest of the formulas had to be adjusted in the same way (to increase the value of the highlighted C by 1.

With the modification the formula looks logical and correct (at least to me); but some of the results didn’t give the result I thought they should.

If we were to zero in on a small portion of this I think I could communicate better. If we just look at Jan 17th start and end dates on the database and 7:30-13:00 on the calendar.

Looking at the raw data on the database I see 10:00 to 10:10 and cumulatively the time between 10:20 to 11:30 should render a result of True. On the Calendar C18 and C20-C27 should
show to be true (at least as I read the formula). The actual results say otherwise (so I’m obviously not interpreting what this formula is doing correctly).
The formula results in C21-C25 to be True (as I thought is should) but it rendered C26 & C27 to be False. I don’t follow this at all and cant see how that formula
would produce a False result in those two fields.
I also don’t see how its not recognizing C18 or C20 as True.

What you’ve come up with has gotten me closer than anybody. I’m truly grateful. Would you please take another look at this with this new information. Thank you for any help you provide.

Sincerely, Karl E