Hello,

I need to calculate a formula which gives me the "time to be completed at" in the last column

Request# Date Requested Priority To be completed at
12345 8/27/2014 4:15 Urgent
12346 8/27/2014 6:48 High
12347 8/27/2014 10:12 Normal
12348 8/27/2014 1:17 Low
12349 8/27/2014 3:00 High


Things to be taken into account:-
Low - 24hrs
Normal - 16hrs
High -8hrs
Urgent - 4hrs

Shift Start time : 6:30am
Shift End Time : 3:30pm

If an high request come at 3:00pm, then the time calculation should be 30mins present day and balance mins the other day resulting in 08/28/2014 2:00pm.

Please suggest the formula to calculate the Time to be completed which includes the start time and end time and also excludes the weekends and holidays.


Regards
Jagadishan