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.