Hi,

I have 2 fields dateSubmit and dateComplete on which I need to calculate TAT in hours, submit and complete dates does not have any time restrictions [can be submitted/completed 24x7]. TAT should be calculated meeting the below criteria

1. Need to exclude weekends (Saturday and Sunday).
2. Down time if any (due to internet/software not working)

An ideal scenario would be as below.
dateSubmit dateComplete TAT
9/5/2014 15:36 9/8/2014 13:19 21:43
9/6/2014 10:06 9/8/2014 12:07 12:07


9/5/2014 12:46 9/8/2014 12:10 23:23
9/6/2014 13:01 9/8/2014 12:15 12:15
9/7/2014 13:46 9/7/2014 11:07 0:00
9/19/2014 9:55 9/21/2014 10:13 14:05
9/8/2014 9:57 9/10/2014 8:07 44:09

Downtime
From To
9/8/2014 15:57 9/10/2014 17:07

Can someone give me a quick formula to calculate this please?

Hi what is TAT ? Pleaser post a sample sheet showing before and after - Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@ Pecoflyer

TAT is turn around time.

Thanks,

Attached Files

Example TAT.xlsx (9.7 KB, 26 views)

Sorry I don't understand what you need, nor how you get your results. Perhaps another member might help

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What he needs is to subtract the Submit date from the Complete date while accounting for weekends and downtime.

There is a function called networkdays which will do this. Unfortunately, it returns only an integer. So for that first line where the start is on Friday and finish is on Monday, networkdays returns 2.

Here's a start. I'm not sure it will lead anywhere.

Anyhoo, what I did was to calculate adjusted Submit and Complete such that anything submitted on the weekend is adjusted forward to the beginning of the work week and anything completed is adjusted back to the end of the work week.

From there, it's a simple subtraction, Complete - Submit...

...except if it spans a weekend (e.g. Fri-Mon, Thu-Tue). For that, it tests if the Weekday(Complete)<Weekday(Submit)...if it is, then subtract 2 (I've taken care of that).

...except if it spans two weekends, well...no dice. May be able to account for this with something like INT((Complete-Submit)/7) which would give the number of whole weeks.

...except if it spans a weekend, but is more than a full week (e.g. Fri-Fri), it won't subtract the weekend time. Or maybe the solution to the previous bullet will cover this.

Still doesn't account for downtime. We can skin that cat later. In the meantime, please test this out. The solution is not as pretty as I would like and I have a feeling that it's leading us down a rabbit hole of exceptions to be accounted for.

Attached Files

TAT.xlsx (12.4 KB, 42 views)