I posted this problem in Mr. Excell a couple weeks ago:
[ “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 IM ATTEMPTING TO UTILIZE THIS FORMULA
On the spreadsheet I’m using 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.” ]
I’ve got more descriptive information of the problem now and hope this will help clarify my issue.
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<TIME(11,20,0),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) are all true, 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
{=SUM(IF(T2=Usage!$E$1000:$E$1999,IF(IF(C25<Usage!$I$1000:$I$1999,C25,Usage!$I$1000:$I$1999)-IF(C24>Usage!$H$1000:$H$1999,C24,Usage!$H$1000:$H$1999)>0,1)))}
Return value of 4
In this example; if formula was entered on the calendar in cell T25 indicating 1/17/2018 as the date, 11:00 as the start time, and 11:10 as the stop time; with all 3 conditions being met the cell would turn return a value of 1 (but somehow it results in a value of 4???
So far all my attempts have failed. What I thought were logical formulas that do not work as I thought they should.
Does anyone out there have any ideas. Any help would be Greatly Appreciated.
Respectfully,
Karl E
<tbody>
</tbody>
<tbody>
</tbody>
[ “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 IM ATTEMPTING TO UTILIZE THIS FORMULA
On the spreadsheet I’m using 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.” ]
I’ve got more descriptive information of the problem now and hope this will help clarify my issue.
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<TIME(11,20,0),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) are all true, 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
{=SUM(IF(T2=Usage!$E$1000:$E$1999,IF(IF(C25<Usage!$I$1000:$I$1999,C25,Usage!$I$1000:$I$1999)-IF(C24>Usage!$H$1000:$H$1999,C24,Usage!$H$1000:$H$1999)>0,1)))}
Return value of 4
In this example; if formula was entered on the calendar in cell T25 indicating 1/17/2018 as the date, 11:00 as the start time, and 11:10 as the stop time; with all 3 conditions being met the cell would turn return a value of 1 (but somehow it results in a value of 4???
So far all my attempts have failed. What I thought were logical formulas that do not work as I thought they should.
Does anyone out there have any ideas. Any help would be Greatly Appreciated.
Respectfully,
Karl E
| E | F | G | H | I | J | |
| Date | Library Resource | VLER | Time In | Time Out | 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 |
<tbody>
</tbody>
| 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 | | | | | | | | | | | | | | | | | | | | | | |
<tbody>
</tbody>
