Weekday/Weekend Formula

Hi nmckever, Please confirm the following conclusions:

Originally Posted by nmckever

Updated Criteria:
Anything submitted between 8am-4pm, Monday - Friday - Mail is responsible
All same day requests - Local is responsible
Same day requests (request date and submitted date are the same) submitted before 8am - Local is responsible
Same day requests (request date and submitted date are the same) submitted after 4pm - Local is responsible
Requests submitted Friday after 4pm,for requests submitted for all day Friday, Saturday, Sunday and Monday of the following week - Local is responsible
Anything submitted between 8am-4pm - Monday - Friday - Mail is responsible
Anything submitted on Sunday between 8a-4pm - Mail is responsible

Reduces to : "All same day requests - Local is responsible"?
Reduces to : "Anything NOT SAME DAY submitted between 8am-4pm, Sunday - Friday - Mail is responsible"?

What does "request submitted for" mean EXACTLY.
Also, what are your labels for columns F, G, and H. Difficult to fix anything if we don't know where the info is.

Please attach a small sample workbook, your thread will get more responses. Thanks - Lee

Paste this in I2 and copy down:
=IF(AND([@[Request Date]]<>[@[Submitted Date]],[@[Submitted Time]]>=8/24,[@[Submitted Time]]<16/24,WEEKDAY([@[Submitted Date]],1)<>7),"Mail","Local")

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

If submitted after 4 pm on Sunday for NEXT Tuesday = "Mail", then this works:

=IF([@[Request Date]]=[@[Submitted Date]],"Local",IF(AND([@[Submitted Time]]>=8/24,[@[Submitted Time]]<16/24,WEEKDAY([@[Submitted Date]],1)<>7),"Mail",IF(OR(AND(WEEKDAY([@[Submitted Date]])>2,WEEKDAY([@[Submitted Date]])<6),AND(WEEKDAY([@[Submitted Date]])=6,[@[Submitted Time]]<8/24)),"Local",IF(([@[Submitted Date]]+MOD(9-WEEKDAY([@[Submitted Date]]),7))<[@[Request Date]],"Mail","Local"))))

If submit date = request date                                              LOCAL
ElseIf submitted on Sunday-Friday after 8am and before 4pm                 MAIL
ElseIf submitted on Friday after 4pm or Saturday, Sunday or Monday...
...AND request date is the following Tuesday or later                      MAIL
Else                                                                       LOCAL

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Ok, If submitted after 4 pm on Sunday for NEXT Tuesday = "Local", then try this: (or see next post for alternative.)

=IF([@[Request Date]]=[@[Submitted Date]],"Local",IF(AND([@[Submitted Time]]>=8/24,[@[Submitted Time]]<16/24,WEEKDAY([@[Submitted Date]],1)<>7),"Mail",IF(AND(OR(WEEKDAY([@[Submitted Date]])=7,AND(WEEKDAY([@[Submitted Date]])=6,[@[Submitted Time]]>=16/24),AND(WEEKDAY([@[Submitted Date]])=1,[@[Submitted Time]]<8/24)),([@[Submitted Date]]+MOD(9-WEEKDAY([@[Submitted Date]]),7))<[@[Request Date]]),"Mail","Local")))

If submit date = request date                                              LOCAL
ElseIf submitted on Sunday-Friday after 8am and before 4pm                 MAIL
ElseIf submitted on Friday after 4pm or Saturday or Sunday before 8am)...
...AND request date is the following Tuesday or later                      MAIL
Else                                                                       LOCAL

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee