#1
|
|||
|
|||
Need a formula to calculate turnaround time in hours
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? |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Quote:
TAT is turn around time. Thanks, |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
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. Last edited by gebobs; 09-29-2014 at 01:30 PM. |
#6
|
|||
|
|||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need a formula to calculate turnaround time | Rockitman31 | Excel | 9 | 09-07-2015 09:41 AM |
turnaround time minutes divided by volume | tweeter | Excel | 9 | 11-18-2013 02:09 PM |
Formula to calculate Date and Time | funkyfido | Excel | 2 | 06-25-2013 04:25 PM |
Calculate difference by days and hours | alysolyman | Excel | 6 | 05-13-2013 02:19 AM |
How to get cells to calculate time? | jrasche2003@yahoo.com | Excel | 2 | 02-09-2007 07:10 AM |