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

Status
Not open for further replies.
K

Karl E

New Member
Joined
Apr 3, 2015
Messages
12
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

[TABLE="width: 491"]
<tbody>[TR]
[TD][/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date
[/TD]
[TD]Library
Resource
[/TD]
[TD]VLER
[/TD]
[TD]Time
In
[/TD]
[TD]Time
Out
[/TD]
[TD]E/T
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1128
[/TD]
[TD]1/12/2018
[/TD]
[TD="colspan: 2"]Employee Computer
[/TD]
[TD]10:30
[/TD]
[TD]11:00
[/TD]
[TD]0:30:00
[/TD]
[/TR]
[TR]
[TD]1129
[/TD]
[TD]1/12/2018
[/TD]
[TD]MHeV Assist
[/TD]
[TD]y
[/TD]
[TD]12:00
[/TD]
[TD]12:10
[/TD]
[TD]0:10:00
[/TD]
[/TR]
[TR]
[TD]1130
[/TD]
[TD]1/12/2018
[/TD]
[TD]internet
[/TD]
[TD][/TD]
[TD]8:30
[/TD]
[TD]10:30
[/TD]
[TD]2:00:00
[/TD]
[/TR]
[TR]
[TD]1131
[/TD]
[TD]1/12/2018
[/TD]
[TD]internet
[/TD]
[TD][/TD]
[TD]8:30
[/TD]
[TD]9:30
[/TD]
[TD]1:00:00
[/TD]
[/TR]
[TR]
[TD]1132
[/TD]
[TD]1/12/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]9:00
[/TD]
[TD]9:10
[/TD]
[TD]0:10:00
[/TD]
[/TR]
[TR]
[TD]1133
[/TD]
[TD]1/12/2018
[/TD]
[TD="colspan: 2"]E-Benefits Assist
[/TD]
[TD]10:00
[/TD]
[TD]10:20
[/TD]
[TD]0:20:00
[/TD]
[/TR]
[TR]
[TD]1134
[/TD]
[TD]1/17/2018
[/TD]
[TD]internet
[/TD]
[TD][/TD]
[TD]11:01
[/TD]
[TD]11:19
[/TD]
[TD]0:18:00
[/TD]
[/TR]
[TR]
[TD]1135
[/TD]
[TD]1/17/2018
[/TD]
[TD]internet
[/TD]
[TD][/TD]
[TD]11:00
[/TD]
[TD]11:30
[/TD]
[TD]0:30:00
[/TD]
[/TR]
[TR]
[TD]1136
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]Employee Computer
[/TD]
[TD]10:30
[/TD]
[TD]11:30
[/TD]
[TD]1:00:00
[/TD]
[/TR]
[TR]
[TD]1137
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]Employee Computer
[/TD]
[TD]10:30
[/TD]
[TD]11:30
[/TD]
[TD]1:00:00
[/TD]
[/TR]
[TR]
[TD]1138
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]10:00
[/TD]
[TD]10:10
[/TD]
[TD]0:10:00
[/TD]
[/TR]
[TR]
[TD]1139
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]10:00
[/TD]
[TD]10:05
[/TD]
[TD]0:05:00
[/TD]
[/TR]
[TR]
[TD]1140
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]10:00
[/TD]
[TD]10:15
[/TD]
[TD]0:15:00
[/TD]
[/TR]
[TR]
[TD]1141
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]10:15
[/TD]
[TD]10:30
[/TD]
[TD]0:15:00
[/TD]
[/TR]
[TR]
[TD]1142
[/TD]
[TD]1/17/2018
[/TD]
[TD="colspan: 2"]MHeV Assist
[/TD]
[TD]10:30
[/TD]
[TD]10:35
[/TD]
[TD]0:05:00
[/TD]
[/TR]
[TR]
[TD]1143
[/TD]
[TD]1/18/2018
[/TD]
[TD="colspan: 2"]Internet Personal Device
[/TD]
[TD]9:00
[/TD]
[TD]10:00
[/TD]
[TD]1:00:00
[/TD]
[/TR]
[TR]
[TD]1144
[/TD]
[TD]1/18/2018
[/TD]
[TD="colspan: 2"]Employee Computer
[/TD]
[TD]9:30
[/TD]
[TD]10:00
[/TD]
[TD]0:30:00
[/TD]
[/TR]
[TR]
[TD]1145
[/TD]
[TD]1/18/2018
[/TD]
[TD]internet
[/TD]
[TD][/TD]
[TD]9:00
[/TD]
[TD]10:00
[/TD]
[TD]1:00:00
[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 627"]
<tbody>[TR]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]AF
[/TD]
[TD]AG
[/TD]
[TD]AH
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 22"] January
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01
[/TD]
[TD]02
[/TD]
[TD]03
[/TD]
[TD]04
[/TD]
[TD]05
[/TD]
[TD]06
[/TD]
[TD]07
[/TD]
[TD]08
[/TD]
[TD]09
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]14
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[TD]29
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[/TR]
[TR]
[TD]7:30
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7:40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:20
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:30
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10:50
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:20
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:30
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11:50
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12:00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16:00
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
272
Cubist
Cubist
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
88
welshgasman
welshgasman

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

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