Updating start times

[Leonidas75]

I am trying to create a formula where hours of business are from Monday 8am through to Friday 11PM uninterrupted. If i receive a ticket inside these hours my target response time is time received + 24 hours

What i am trying to calculate is if i receive a ticket outside of hours (say Friday 11.30pm). the ticket start time should begin on Monday 8am with response target Tuesday 8am (Public Holidays should be included).

If i receive a ticket on Friday 10PM then i have until Monday 10PM to respond (or Tuesday 10PM if monday PH).

so i need to show 2 columns - start time and target time.

 

[Leonidas75]

There could be potential for overtime so maybe anything up to friday 07:59 will have a target to Saturday 07:59.

Friday 08:30 would be Monday 08:30

 

[Leonidas75]

struggling to put the formula together, especially with the named formulas where they are referencing A19 onwards....

 

[Crystalyzer]

When creating the formulas in the formula manager, you must make sure that the active cell is the first cell in the range you eventually want that formula to relate to and that all the $ are in the right places to anchor the appropriate cells. Then when you insert the named formula in your formula all the references will line up.