the formula can be verified.
try using toolsformula auditingevaluate formula..

=if(and(condition_1,condition_2),"On Time","Closure Time")

For the "And(true,true)"

condition_1 :
"HOUR(A2)=7" : the Hour should be equal or past 7:00
AM.(e.g.7AM,7:30AM,11:30AM,6PM...)

condition_2 :
"ROUNDDOWN((A2-INT(A2)),8)
<=
(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{16,20,20,20,2 0,20,15}))/24"

when A2 is Monday with time at 20:00:01 or 8:00:01PM:
-------------Hour(A2)<=20 : the answer will be true, disregarding the
00:00:01 which was delimited by the Hour() function...as an hour integer
so, to provide more sensitivity on the formula, suggested was to use
"ROUNDDOWN((A2-INT(A2)),8) instead of Hour(A2)
for A2 with 20:00:01 or 8:00:01PM
ROUNDDOWN((A2-INT(A2)),8) = 0.8333449
this is 0.8333449 of 1 day
With this condition_2, the answer must be "False"
but using "=HOUR(A2)<=20" : the condition will be "True", which must not be.

For the Lookup
(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{16,20,20,20,2 0,20,15}))/24" : the curly
brackets contains the constants (numbers in this case).
{1,2,3,4,5,6,7} : Excel strores the weekdays numbered as 1 to 7, which
corresponds to Sunday thru Saturday.
{16,20,20,20,20,20,15} : the end hour for each day (sunday-saturday) that
limits the "On Time" criteria.
When Lookup found a match on Weekday(A2) = {1 or 2 or 3 or 4 or 5 or 6 or 7},
it will pull-out one value from {16,20,20,20,20,20,15}.
Meaning
if Weekday(a2) =1, Lookup result = 16
if Weekday(a2) =2, Lookup result = 20
if Weekday(a2) =7, Lookup result = 15

the lookup result will then be divided to 24, and convert the lookup result
into a portion of a day..

Try to segregate each functional formula and later merge them into one
formula, so you can verify and learn from it.

Regards and thanks for the feedback.

"Titanium" wrote:

I hate to ask this, but would you mind explaining the formula to me in words.
I'm a little lost as to how the AND, ROUNDOWN,INT & LOOKUP operate. Also,
what is the purpose/functionality of the curly brackets? I have looked them
all up in Excel Help, but I'm not quite making sense of them. I've decided to
go with this formula as it does suite me best, so I'd really like to get a
handle on what all I'm typing so the next time I have a similar problem to
solve I won't have t look for help. Thanks in advance for all your help.

"fair_thumb" wrote:

one way
with date on A2 formatted as "ddd, mm/d/yy, h:mm"

=IF(AND(HOUR(A2)=7,ROUNDDOWN((A2-INT(A2)),8)<=(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{ 16,20,20,20,20,20,15}))/24),"On Time","Closure Time")

the 20:00 is tricky! <g

"Titanium" wrote:

Column A has the date and time displayed: Thu, 06/14/07, 11:15
Column B needs to calculate: "If (A=Mon to Fri from 7:00 to 20:00, Sat from
7:00 to 15:00, Sun from 7:00 to 16:00 Then Display in Column B "On Time")
If False then Display in Column B "Closure Time")

I don't even know where to start with this one. Any assistance would
certainly be appreciated. Thanks in advance for your help.

I wasn't aware of that feature. Thanks so much for all your help!

"fair_thumb" wrote:

the formula can be verified.
try using toolsformula auditingevaluate formula..

=if(and(condition_1,condition_2),"On Time","Closure Time")

For the "And(true,true)"

condition_1 :
"HOUR(A2)=7" : the Hour should be equal or past 7:00
AM.(e.g.7AM,7:30AM,11:30AM,6PM...)

condition_2 :
"ROUNDDOWN((A2-INT(A2)),8)
<=
(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{16,20,20,20,2 0,20,15}))/24"

when A2 is Monday with time at 20:00:01 or 8:00:01PM:
-------------Hour(A2)<=20 : the answer will be true, disregarding the
00:00:01 which was delimited by the Hour() function...as an hour integer
so, to provide more sensitivity on the formula, suggested was to use
"ROUNDDOWN((A2-INT(A2)),8) instead of Hour(A2)
for A2 with 20:00:01 or 8:00:01PM
ROUNDDOWN((A2-INT(A2)),8) = 0.8333449
this is 0.8333449 of 1 day
With this condition_2, the answer must be "False"
but using "=HOUR(A2)<=20" : the condition will be "True", which must not be.

For the Lookup
(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{16,20,20,20,2 0,20,15}))/24" : the curly
brackets contains the constants (numbers in this case).
{1,2,3,4,5,6,7} : Excel strores the weekdays numbered as 1 to 7, which
corresponds to Sunday thru Saturday.
{16,20,20,20,20,20,15} : the end hour for each day (sunday-saturday) that
limits the "On Time" criteria.
When Lookup found a match on Weekday(A2) = {1 or 2 or 3 or 4 or 5 or 6 or 7},
it will pull-out one value from {16,20,20,20,20,20,15}.
Meaning
if Weekday(a2) =1, Lookup result = 16
if Weekday(a2) =2, Lookup result = 20
if Weekday(a2) =7, Lookup result = 15

the lookup result will then be divided to 24, and convert the lookup result
into a portion of a day..

Try to segregate each functional formula and later merge them into one
formula, so you can verify and learn from it.

Regards and thanks for the feedback.

"Titanium" wrote:

I hate to ask this, but would you mind explaining the formula to me in words.
I'm a little lost as to how the AND, ROUNDOWN,INT & LOOKUP operate. Also,
what is the purpose/functionality of the curly brackets? I have looked them
all up in Excel Help, but I'm not quite making sense of them. I've decided to
go with this formula as it does suite me best, so I'd really like to get a
handle on what all I'm typing so the next time I have a similar problem to
solve I won't have t look for help. Thanks in advance for all your help.

"fair_thumb" wrote:

one way
with date on A2 formatted as "ddd, mm/d/yy, h:mm"

=IF(AND(HOUR(A2)=7,ROUNDDOWN((A2-INT(A2)),8)<=(LOOKUP(WEEKDAY(A2),{1,2,3,4,5,6,7},{ 16,20,20,20,20,20,15}))/24),"On Time","Closure Time")

the 20:00 is tricky! <g

"Titanium" wrote:

Column A has the date and time displayed: Thu, 06/14/07, 11:15
Column B needs to calculate: "If (A=Mon to Fri from 7:00 to 20:00, Sat from
7:00 to 15:00, Sun from 7:00 to 16:00 Then Display in Column B "On Time")
If False then Display in Column B "Closure Time")

I don't even know where to start with this one. Any assistance would
certainly be appreciated. Thanks in advance for your help.