T
The Narcissist
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(E6>TIMEVALUE("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.
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(E6>TIMEVALUE("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.