Add working hours only to a date and time

[AllanWilliams]

I have a spreadhseet which records calls received by my office and monitors response times. I have two columns that record the date and time the call is received. I have a priority column where the user can select the urgency of the call from a drop-down list (Critical, High, Medium, Low). The next column shows the estimated fix time based on the priority.

I want to enter a forumla in the next column that adds an amount (say 1 hour) to the received date and time. However, i only want to include working hours in this addition - 09:00 till 17:00, Monday till Friday.

Any ideas?

Thanks!

[kraljb]

Date in Column A, Time in Column B

=IF(AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7,HOUR(B1)>=9,HOUR(B1)<17),B1+TIME(HOUR(B1)+1,MINUTE(B1),SECOND(B1)),"Non-Working")

To Modify the time added change the HOUR(B1)+1 to whatever you need it to (or add minutes/seconds as desired)

Hope that helps...
John

[Simon Itodo]

Hello,

I tried the solution above and I kept getting answer "FALSE"

I have attached my sheet, should you want to take a look at it please.

Help needed

[Shareez Saleem]

Click F2 and see the linked cells in this formula.

=IF(AND(WEEKDAY(A2)>1,WEEKDAY(A2)<7,HOUR(B2)>=9,HOUR(B2)<17),B2+TIME(HOUR(B2)+9,MINUTE(B2),SECOND(B2)))

You have pasted the above formula in C1 instead of C2.

[Simon Itodo]

Hello,

Thank you for your response. I have made the changes, however the result remains same