Subtract date/time, but not include non-business hours

[smllchng5]

So I am fairly new to excel formulas, and have been asked to figure out the turn around time for projects in decimals. I have the following formular for if it is completed the same day (=(L3-F3)*24/8 )....so you can see that it works fine for the first 3 rows, but since the 4th one was started close to the end of the shift (Shift is 7:30am - 4:30pm) and then resumed at 9:30 am the next business day....how can I write a formula to take into account those hours not worked. Also,. what if it is a week-end. Any help would be greatly appreciated. TIA

 

[Ezguy4u]

smllchng Let's get the ball rolling. Here is my solution to your problem. It's in a very rough form but we have to start somewhere. Now this works for some of the scenarios but not all of them. As you think of more criteria you let us know. I do have one suggestion, if you do have a job that takes 2 days, can you put them on 2 lines. Anyway this usually generates more questions than answer so let the discussion begin.

24-10-07.xlsx
	E	F	G	H	I	J	K	L
1	Date Delivered	Time Delivered	Date Completed	Group Complet ed time (PST)	Status	TAT	7:30 AM	4:30 PM
2
3	9/11/2024	10:00 AM	9/11/2024	1:00 PM	Completed	0.38	0.38
4	9/13/2024	9:00 AM	9/13/2024	2:00 PM	Completed	0.63	0.62
5	9/13/2024	10:00 AM	9/13/2024	3:30 PM	Completed	0.69	0.69
6	9/23/2024	4:30 PM	9/24/2024	9:30 AM	Completed	-0.88	0.25
7	9/27/2024	9:00 AM	9/27/2024	11:15 AM	Completed	0.28	0.28
8	9/28/2024	7:30 AM	9/28/2024	4:30 PM			1.13
9	9/29/2024	7:30 AM	9/29/2024	9:30 AM			0.25
10	10/1/2024	3:30 PM	10/2/2024	9:30 AM			0.38
11	10/3/2024	3:30 PM	10/3/2024	4:30 PM			0.13
12	10/4/2024	7:30 AM	10/4/2024	9:30 AM			0.25
Data

Cell Formulas
Range	Formula
K3:K12	K3	=IF(G3>E3,($L$1-F3)*24/8+(H3-$K$1)*24/8,(H3-F3)*24/8)