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" wrote:

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" wrote:

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" wrote:

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

"JMB" wrote:

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")



"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.