Need help with Dates - Workday Function

[Simon Itodo]

Literally, I need someone to save me here. Every month I have to do a performance report using excel and working with dates is really killing me.

This is the problem.

Working hours is 8am-5pm (weekdays only) and I have several Turn Around Times for different activities. I need to do two things. Add TAT to Logged Date to give me Expected Resolution date. Problem is when I use Workday function it returns the correct date but the time is always 0:00 . Which implies if a service request is expected to be resolved at 9am on that day, it would be considered to have breached TAT.

I think in summary, I just need my workday function to also return results with the correct time, not just day. See sample table 7

Logged Date = Date service request was logged
Solved on = Date service request was solved
Expected Breached date = (Logged on Date + Given TAT)
SLA MET = IF(Solved on > Expected Breached Date, "NO","YES")
SLA MET = is in hours (27 hours is 3 working days)

Please, see attachment for sample data

[Glenn Kennedy]

Hi there. the formula needed to do this is a bit of amonster, so I have not included it here. You'll find it in the attached sheet. Do you need to take national holidays into account??

[Simon Itodo]

Thank you Glenn.

Like you said, it is one massive formula. Trying to understand the thought behind it now. However, I think you worked on getting the time taken between start time and end time, what I really need is the END Time.

[Glenn Kennedy]

Doesn't it get you to the same result, though. I calculated the number of working hours between the logged date and the solved date and compared that to the TAT and arrived at a Yes/No answer. How is that different? Confused...

[Simon Itodo]

To be honest, you just explained it now. I now understand and it works great. Thank you very much.

However, for openness and clarity to the people I am appraising, especially for service requests that have breached I like to let them know the last possible minute to solve a service request which is the Expected Breached Date.

Still, thank you very much.

[Glenn Kennedy]

... in which case... this in C2 (not a monster!!):

=WORKDAY(A2,3)+MOD(A2,1)

[newdoverman]

If you are just wanting the time past due this will do it formatted as [h]:mm:ss

Please Login or Register  to view this content.

[Simon Itodo]

and it worked. Thank you very much. I am very grateful.

You have no idea how much time you have saved me on a monthly basis