How to solve working hours/business days query

[Gaz863]

Hello!

I am currently working on a spreadsheet that will be able to log certain business processes and target times for them to be achieved. Basically, whenever a call comes in, we register it on the spreadsheet then have 3 hours max to reply to it. e.g. call comes in at 10:00am, we have a target of 1:00pm to solve it.

The problem I am facing is we work within regular working hours (9:00am - 5:00pm Monday to Friday), so for instance if a call comes in at 4pm Friday, we have until 11:00am Monday to solve it (an hour on Friday upto 5pm and then 2 hours on Monday from 9am-11am). Hope that makes sense!

I was wondering if there was any formula or code for a button I could use that would incorporate business days and a 9-5 rota for this target??

Any help would be greatly appreciated!!

P.S. I am currently using the formula: =IF(B4="","",B4+TIME(3,0,0)) in order to find the time 3 hours in advance.

[p24leclerc]

Hi, look at the attached worksheet.
there is a formula that gives you the target response time.
If you keep a record of your calls, you'll have to copy the value of the cells in order to keep actual target and prevent it to be reevaluated with the date of tomorrow.

Notice the Shift end cell is calculated with the actual date.
Hope this help
Pierre

[Gaz863]

Pierre you are a genius! That is exactly what I was looking for, thank you so much!!

[Alf]

I see you got help but perhaps this file could be of interest as well?

Alf