Paul,
based solely on the examples provided...
C2:
=IF(AND($A2="Red",MOD($B2,1)<="15:00"+0,NETWORKDAYS($B2,$B2,$E$1:$E$10)=1),$B2+"1:00",WORKDAY($$B2,1,$E$1:$E$10)+IF($A2="Red","9:00",MOD($B2,1)))
copied down to C13
where E1:E10 would contain public holiday dates
the above replicates your expected results.
Note use of NETWORKDAYS & WORKDAY functions necessitates activation of the Analysis ToolPak (aka ATP) Add-In (Tools -> Add-Ins).
If you are doing a lot of date based calculations I would strongly advise installing this as it will simplify your calculations.
You can achieve the same without ATP, however, should you wish/need to incorporate Public Holidays into the above then things become a little convoluted.
If you don't need to worry about Public Holidays then you can avoid ATP functions quite simply:
C2:
=IF(AND($A2="Red",MOD($B2,1)<="15:00"+0,WEEKDAY($B2,2)<6),$B2+"1:00",INT($B2)+LOOKUP(WEEKDAY($B2),{1,6,7},{1,3,2})+IF($A2="Red","9:00",MOD($B2,1)))
copied down to C17
Bookmarks