Tricky IF/Then Date based formula needed

[Titanium]

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.

 

[Don Guillett]

First, Look at the AND function. Then,the OR function and then the IF
function. Put together in that order.

 

[JMB]

This seems to work, but if the time is exactly 20:00 (for Monday) do you want
"Open" or "Closed"??

If you want Closed, you could try:
=IF(AND(HOUR(A16)>=7,HOUR(A16)<=19+LOOKUP(WEEKDAY(A16,2),{5,6,7},{0,-5,-4})),"On Time","Closure Time")

If you would want open for that example:
=IF(AND(HOUR(A16)>=7,HOUR(A16-1/86400)<=19+LOOKUP(WEEKDAY(A16,2),{5,6,7},{0,-5,-4})),"On Time","Closure Time")

 

[Arvi Laanemets]

Hi

Add an additional sheet (p.e. Times - you can hide it later)
On sheet times, create a table
1 16:00
2 20:00
7 17:00

(In column A day numbers for Sunday, Monday and Saturday, in column B
according closure times)

Now your formula for B2 will be
=IF(AND(MOD(A2;1)>=(7/24),MOD(A2,1)<=CHOOSE(MATCH(WEEKDAY(A2),Times!$A$1:$A$3,1),16/24,20/204,17/24)),"On
Time","Closure Time")
etc.


Arvi Laanemets

 

[fair_thumb]

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]

JMB, Closed at 20:00. Thanks so much for your reply!

 

[Titanium]

Thanks so much for your reply!

First, Look at the AND function. Then,the OR function and then the IF
function. Put together in that order.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Titanium]

Thanks so much for your reply! I'll try your method and get back to you. So
many replies and so many different ideas. All appreciated.

 

[Titanium]

Thanks so much for your reply! That is an interesting approach. I will try it
and get back to you. I can't believe so many replied.

 

[JMB]

I noticed a small error. The 5 in {5,6,7} should have been a 0 (not sure how
that got there). Also, I notice the 19+Lookup(...) can be made shorter. My
revised suggestion would be:

=IF(AND(HOUR(A16)>=7,HOUR(A16)<LOOKUP(WEEKDAY(A16,2),{0,6,7},{20,15,16})),"On Time","Closure Time")

Sorry about that.

 

[Titanium]

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.

 

[Titanium]

Thanks for the update. I'll try your formula out again.

 

[fair_thumb]

the formula can be verified.
try using tools>formula auditing>evaluate 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,20,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,20,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]

If you don't mind can you please explain this portion of your formula:
<LOOKUP(WEEKDAY(A16,2),{0,6,7}

I don't quite understand the ,2 after the A16 - what does it signify?

Also, the 0, does it stand for all of the weekdays?

Again, thanks in advance for your assistance.

 

[JMB]

The weekday function returns a number (1-7) to indicate which day of the week
it is. The second parameter of the weekday function dictates which day is
considered number 1.

If the second parameter of weekday is:
1 or omitted - Returns Numbers 1 (Sunday) through 7 (Saturday).
2 - Returns Numbers 1 (Monday) through 7 (Sunday).
3 - Returns Numbers 0 (Monday) through 6 (Sunday).

Since your weekdays all have the same operating hours, it makes sense to
group them together (I used option 2, although option 3 could also work, but
{0,6,7} would have to be changed to {0,5,6}).

And, yes the 0 represents all of the weekdays. If Lookup can't find the
lookup value, it matches to the largest number that is less than or equal to
the lookup value(You could also use a 1 instead of 0 - I used 0 out of
habit). So if Weekday returns 1-5, Lookup will match to the 0 and return the
first number from the second list (20).

 

[Titanium]

Thanks so much for the explaination. That's great!

 

[Titanium]

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

 

[JMB]

Quite welcome. Thanks for the feedback.

Thanks so much for the explaination. That's great!

 

[Titanium]

I hate to bother you again, but I've been trying to make a modification to
this formula, but I can't figure it out.

This is what I came up with:


=IF(AND(HOUR(A16)>LOOKUP(WEEKDAY(A16,2),{0,6,7},{5,7,7})),HOUR(A16)<LOOKUP(WEEKDAY(A16,2),{0,6,7},{20,15,16})),"On Time","Closure Time")


The operation time is different from weekends to weekends. Weekdays start
time is 05:00 and weekends start at 07:00. Condition #1 is the only change I
need to make. Could you please help me with this. Again, thanks so much for
all your assistance.



========

 

[JMB]

I think you just added an extra ")" at the end of the first lookup. And you
probably want >= for the first condition if it is "open" at exactly 5:00 or
7:00.

=IF(AND(HOUR(A16)>=LOOKUP(WEEKDAY(A16,2),{0,6,7},{5,7,7}),HOUR(A16)<LOOKUP(WEEKDAY(A16,2),{0,6,7},{20,15,16})),"On Time","Closure Time")