Turnaround Time Formula needed

[dawwni]

I need a formula that calculates the turnaround time in excel in hours between two dates and times. For example, A1 is the date received, A2 is the time received, A3 is date completed and A4 is the time completed. This would be for a work day of either 8 to 10 hours depending upon location so I would assume there is an if/then in there somewhere too. Any help is greatly appreciated.

 

[awoohaw]

Also, the turn around time is not just during business working hours though. It starts when we receive the sample and ends when results are sent. So even during the overnight hours, those are still accumulated into the total turnaround time.

sorry, i've been sidetracked. I will work on this in the evening (in 4-6 hours). Others may post their ideas on the issue in the mean time.

 

[dawwni]

thank you no rush.

 

[awoohaw]

Also, the turn around time is not just during business working hours though. It starts when we receive the sample and ends when results are sent. So even during the overnight hours, those are still accumulated into the total turnaround time.

i just re-read this. so, a project is completed at 15:00 on a tuesday, and was received at 10:00 on a monday ... on the clock that is 29 hours, but since only 10 hrs per day is the TAT 20 hrs (10am-10pm Mon, 12:00 am-10am Tues)? Can you give me more examples?

 

[dawwni]

Sure. As for the one you created it would still be 29 hours for the turn around. But if this was over a weekend, those hours wouldn't be counted.

Ex: Received Friday at 8:00, Completed Monday at 8:00 then Turnaround time would be 24 hours
Ex: Received Tuesday at 8:00, Completed Wednesday at 8:00 = 24 hours
Ex: Received Wednesday at 8:00, Completed Friday at 8:00 = 48 hours
Ex: Received Thursday at 8:00, Completed Monday at 8:00 = 48 hours

I am so sorry, I know this is confusing which is one of the reasons I am having such a hard time getting it myself. I hope this helps.

 

[awoohaw]

Well, questions clear up confusion! Please don't worry. Your last post makes things more clear! hold on...

 

[fxjunction]

Date Received	Time Received	SOD	EOD	Date Completed	Days in Flight	Time Completed	TAT in Hrs	Working Hours per Day
1/3/2023​	13:00​	8:00​	18:00​	1/3/2023​	0.00​	14:00​	1.00​	10:00​
1/3/2023​	8:00​	8:00​	18:00​	1/4/2023​	1.00​	10:00​	12.00​	10:00​
1/3/2023​	11:00​	8:00​	16:00​	1/4/2023​	1.00​	8:00​	5.00​	8:00​

In the TAT column I put the following formula:

=HOUR(IF(A4=E4,G4-B4,(G4-C4)+(D4-B4)+((F4-1)*I4)))
I had to add a few columns to make it work with the formatting, feel free to change your start and end of day times. I also added a days in flight column to make some of the time formatting easier, but it may not be needed with a more elegant solution.

 

[fxjunction]

Date Received	Time Received	SOD	EOD	Date Completed	Days in Flight	Time Completed	TAT in Hrs	Working Hours per Day
1/3/2023​	13:00​	8:00​	18:00​	1/3/2023​	0.00​	14:00​	1.00​	10:00​
1/3/2023​	8:00​	8:00​	18:00​	1/4/2023​	1.00​	10:00​	12.00​	10:00​
1/3/2023​	11:00​	8:00​	16:00​	1/4/2023​	1.00​	8:00​	5.00​	8:00​

In the TAT column I put the following formula:

=HOUR(IF(A4=E4,G4-B4,(G4-C4)+(D4-B4)+((F4-1)*I4)))
I had to add a few columns to make it work with the formatting, feel free to change your start and end of day times. I also added a days in flight column to make some of the time formatting easier, but it may not be needed with a more elegant solution.

This would not factor weekends into account btw.

 

[dawwni]

Thanks so much for your help. I have another question but will start a new post since it is different senario.