A problem with Nested IFs
 

Hi Everyone,

I'm working on a report that looks like this.

D E F
G H
Request Date Request Time Dispatch Date Dispatch Time
Delay
6
7
8
9
10

The problem lies with the delay column. If the Request Time (Column F) has a
value upto 3:30 PM, the dispatch should be done on the same day. If the
request time is after 3:30 PM, the dispatch needs to be done by 11:00 AM the
next day. We do not work on saturdays and sundays. The delay needs to be
calculated in the number of days.

I've been able to manage a formula that calculates the delay but doesn't
account for the weekends. It goes like this :

=IF(OR(F6=D6,AND(E6TIMEVALUE("3:30 PM"),G6<TIMEVALUE("11:00
AM"),F6-D6=1)),"0",F6-D6)

Can someone please help me with an extension to this formula that accounts
for the weekends as well? (Saturdays and Sundays are not to be counted in the
delay).

Thanks a million in advance. :)