| Home |
| Search |
| Today's Posts |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky IF/Then Date based formula needed
Superb! Thanks so much.
"JMB" wrote: 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},{2 0,15,16})),"On Time","Closure Time") "Titanium" wrote: 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},{2 0,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" wrote: 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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
| Date formula needed | Excel Worksheet Functions | |||
| Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
| Date formula needed | Excel Discussion (Misc queries) | |||
| Tricky formulas needed | Excel Worksheet Functions | |||
Threaded Mode
