Excel Turn Around Time Calculation

[rahul_r79]

Hi
I am trying to calculate the turn around time in excel
I have Date Recd and Time Recd and Finished Date and Finished Time
How to find the turn around time so that it automatically remove the
Weekends from the calculation for turn around time and working hours
8:30 AM to 5:00 PM

For Ex
Date Recd : 07/05/2010
Time Recd : 11:30 PM
Finished Date :10/05/2010
Finished Time : 4:00 PM

Then turn around time should be around 13.5 hours.

[p45cal]

If TimeRecd was 11:30 AM wouldn't that would be 13 hours-ish? (You have PM.)

How do you want to see your answers?
Say you had a received time of 9:30 am on the first day and the end point was 4pm (16:00) the next, that'd be some 15 hours, do you want to see 1 day 6.5 hours, or just 15 hours
What if there were a week or more, do you want to see 5 days 3 hours (say) or 45.5 hours?

[rahul_r79]

Hi I want to see it like 45.5 hours as it should only count the working hours and avoid the non working hours as there is 7.5 hours in a day to work so 37.5 +3 = 40.5

[Aussiebear]

Quick question. If the working hours are 8.30am to 5.00pm as stated in post#1, and yet you are saying there are only 7.5 working hours in a day in post #3, you effectively lose an hour somewhere in the day. This could effect the value "turn around time" would it not?

[p45cal]

You worry me. Is there a lunch break to take into account?
8:30am to 5:30pm = 8.5 hours. So lunch of one hour?

Also, could you confirm this:
If TimeRecd was 11:30 AM wouldn't that would be 13 hours-ish? (You have PM.)


Hi I want to see it like 45.5 hours as it should only count the working hours and avoid the non working hours as there is 7.5 hours in a day to work so 37.5 +3 = 40.5

[rahul_r79]

Yes I have to count as 7.5 hours as 1 hour is for break time.Yes sorry my mistake it is 11:30 AM

[p45cal]

This solution gives the answer in hours and minutes because the cell is formatted as
[hh]:mm
but the value behind is in Excel time where 1=24 hours.
If you intend using this in calculations then we may need to alter it.
Assuming your values are in cells B1:B4 in the order you have them then this formula should do it:
=MIN(MAX(17/24-B2,0),8.5/24)+MAX(NETWORKDAYS(B1+1,B3-1),0)*8.5/24+MIN(MAX(B4-8.5/24,0),8.5/24) With your data (11:30 PM version) gives 07:30 formatted, 0.3125 unformatted
and with the 11:30 AM version gives 13:00 formatted and 0.54167 unformatted.

You can easily take account of bank holidays and the like as that facility is available in the NetWordDays function.

Note that if you want to tweak the formula, there are several instances of 8.5, some meaning 8.5 hours in the working day, but only one meaning 8:30 am in red. The 17 refers to 17:00 (5pm).

[p45cal]

bugger. This could make it awfully complicated. I'm bushed, will revisit tomorrow if I have the heart.

So if something arrives at 12:00AM which is say at start of lunch time, and something arrives at 1:00PM (end lunch), and they both finish at the same date/time, they'll both have the same turnaround time? Yuk. Will need to know rigid lunch time start/finish.


Yes I have to count as 7.5 hours as 1 hour is for break time.Yes sorry my mistake it is 11:30 AM

[rahul_r79]

hi
if the start date 7/05/2010

start time :8:30 AM and finish DAte : 07/05/2010 ;Finish Time : 10:30 AM
Then turn around should be around 2 hours, but when i use the above formula it gives around 9:30 , Is I am doing something very stupid ???

[Aussiebear]

The formula takes into account the 1 hour lost for the lunch break. This is exactly why we were asking you about the missing hour.

Once we know when this "lunch break hour" takes place then the formula can be provided.

[lynnnow]

From what I am guessing, there is a half hour lunch break and two 15-minute snack/tea breaks, which would be split through out the 8.5 hours working time.

[p45cal]

The only thing you are doing stupidly is properly testing it!
Test this instead:

=NETWORKDAYS(B1,B3)*8.5/24-MIN(MAX(B2-8.5/24,0),8.5/24)*NETWORKDAYS(B1,B1)-MIN(MAX(17/24-B4,0),8.5/24)*NETWORKDAYS(B3,B3)The red 8.5 is the only ref to an 8:30am start, the rest are 8.5 working hours in a day.

If lunch times are to be taken into account I would go down the user-defined function route.
Meanwhile, if you changed the non-red 8.5s to 7.5 you'd be getting close to a correct turnaround time but it wouldn't take account lunch times on the start/finish days.

If
there is a half hour lunch break and two 15-minute snack/tea breakshahahahahahahahahahahahahaaaaaaaghgh......




hi
if the start date 7/05/2010

start time :8:30 AM and finish DAte : 07/05/2010 ;Finish Time : 10:30 AM
Then turn around should be around 2 hours, but when i use the above formula it gives around 9:30 , Is I am doing something very stupid ???

[rahul_r79]

If ask the guys to put there lunch break also in the spreadsheet in different cell like
Date Recd : 07/05/2010
Time Recd : 11:30 AM
Finished Date :10/05/2010
Finished Time : 4:00 PM
Break Start Time: 1:00 PM
Break Finish Time: 2:00 PM
but then the break times will keep changing for everyone as they work in roster, so that mean I need to write a user defined function which takes input as Start time ,finish time,start date ,finish date and break start time and break finish time???

[rahul_r79]

.... I want to thanks everyone for there help and support...i think the function becomes too much complicated ,But i think the above solution give close enough to Turn Around Time....Thanks A Lot:bow: :bow: