Help needed: SLA & Turnaround Time Calculation

Hi,

It seems a complicated formula and I'm by no means clear why the Median is relevant.
Can you upload the workbook with some examples so that we can examine further, and explain of possible why the use of Median might be relevant?

You mention start and end times, Do you literally mean time only, i.e. no date, or do these P2 & Q2 cells contain a complete date/time number?

The Networkdays bit strips out the number of non working days, which to Excel means Saturdays and Sundays, and the Holiday!H3:H16 reference is to a table of public holidays which are also excluded from the calculation

Hi,

I don't know why the Median function is in there but try this shorter formula

Formula:

Please Login or Register  to view this content.

The formula uses NETWORKDAYS() to determine the absolute number of days between the two dates and multiplies this by 8 being the number of hours between 9:00 and 17:00

It then uses

Formula:

Please Login or Register  to view this content.

to work out the number of hours on the first day between the start time and 9:00 and deducts this number of hours if this calc is positive.
It then uses the similar

Formula:

Please Login or Register  to view this content.

to deduct the number of hours between the the end time and 17:00 and deducts this number.