Calculacting turn around time

[Equinox]

hi

Need help in calculating Turn around time in excel with following conditions

1: Clock shoud tick monday to friday 8:00am to 4:pm only
2: there are 4 coloums where date recieved, date sent, time recieved, time sent are available.
3: the TAT should show the number of hours taken to from the time a case has been recieved to the time it has been sent

Well I am a first timer so can some one please help on this one

[oldchippy]

With start date in A2, end date in B2, start time in C2, end time in D2, put this in E2, format the cell to Custom [hh]:mm

For example
A2 = 01/05/2007 (dd/mm/yyyy)
B2 = 04/04/2007
C2 = 09:00
D2 = 16:00
E2 = B2/24*8-A2/24*8+D2-C2

Result 25:00

[Equinox]

would want to check if the formula takes into consideration the weekens factor as well?

[daddylonglegs]

You can use NETWORKDAYS formula from Analysis ToolPak add-in. Assuming your received and sent times/dates are all within your working days/hours

=NETWORKDAYS(A2,B2)/3-1/3+D2-C2

format result cell as [h]:mm

If received/sent times might be at weekends or evenings then you'll need a slightly more complex formula......

NETWORKDAYS will also allow you to exclude listed holidays, if you wish. If that formula gives you #VALUE! error you need to install Analysis ToolPak - you should be able to do that by using Tools > Add-ins > Tick "Analysis ToolPak"

[Equinox]

Hi

Sorry But the recieved time and date can be anytime even weekends and 24 hrs but time sent is between working hours always

[daddylonglegs]

In that case try

=NETWORKDAYS(A2,B2)/3-1/3+D2-MEDIAN(NETWORKDAYS(A2,A2)*C2,1/3,2/3)