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]

Something like this:

Mr Excel Questions 3.xlsm
	A	B	C
1	Start	End	Time Length
2	02/10/2023 4:29	02/10/2023 20:45	16:16:00
Sheet19

Cell Formulas
Range	Formula
C2	C2	=B2-A2

Format column C for 24 hour time. Start and End are no more than 24 hrs apart.

 

[dawwni]

Thank you. What about if I need to calculate a turnaround time from date/time received to date/time completed for a workday when some sites have a 10 hour day and some have a 8 hour day? We also have incidents of partials that need their own time as well. The turnaround time would only include working hours.

Example:

Date Received	Time Received	Date Completed	Time Completed	Date Received (Partials Only)	Time Completed (Partials Only)	# of Partial Samples	TAT in Hrs	Working Hours per Day
1/3/23	13:00	1/3/23	14:00					10
1/3/23	8:00	1/4/23	10:00					10
1/3/23	11:00	1/4/23	8:00					8
1/3/23				1/5/23	9:00	1		8
1/3/23	14:00	1/5/23	13:00					8
1/3/23								8

 

[awoohaw]

For the examples above, can you give the expected results?
Also, Does work go over weekends or holidays?

Not sure what a Partial is. How should I be concerned with a "Partial"?

If you have holidays and weekends I need to know your holidays for any of the days that are in your sample ranges, if not, make some up for me so it can be explained and you'll know how to apply it with real data.

 

[awoohaw]

In addition to the questions in Post#4, since there is a cap on hours per day, the times the work can be done are needed. I guess this could be the start time of day, but I don't know if that varies either. I'm thinking an 11 hour task could look like 2 + days on a calendar if recieved 1 hr before End of Day and finished 2 hr after Start of Day. 2 days later.

 

[dawwni]

Thank you for you help. A partial is basically a delayed sample so I want to calculate their turnaround times separate from a regular turnaround time. These are currently on the same row just in different columns but moving forward I think separating them to a new row is better for formulas.
Hours for work would still be either 8-10 hours each day so I am not sure if you need start times but lets say 6am-3pm for 8 hour day and 6am-4pm for a 10 hour day.
As for Holidays, I think the excel set ones (major holidays) will work and Saturday and Sunday would be considered weekend.

 

[awoohaw]

so, all projects by the same person starts at the same time, 6 am? But, maybe i'm thinking in terms of Gantt charts and time flow. But, if you know a task cannot start until 12:00 pm then a 4 hour project will go into the next working day.

As far as holidays... excel doesn't know what they are, you have to have some kind of holiday table stored somewhere. as far a weekends, it has a default of Sat/Sun.

 

[awoohaw]

... and you do show differing times recieved. If that is your start time, then you have already given it to me. I think I can work with that.

 

[dawwni]

Yes this is for a lab that receives samples throughout the day so the start time would also be the received time for that particular day. Once results are reported it has been completed. Samples received after a certain point are held until the following day. Some samples require additional prep times which is what a delayed/partial is. My apologies, I know in a simple turnaround formula you can add Holiday at the end in excel. We can just exclude holidays for now. Hopefully this makes better sense. Thank you again for all your assistance.

 

[dawwni]

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.