Lapsed days as hours and taking weekends in to consideration.

[ayeready]

Hi all

I'm trying to work out lapsed hours between 2 date stamps (Column H & Column N) that takes into account the possibility that a weekend will have a baring on the calculation.
This is what i have come up with to date but I've noticed that my calculation doesn't take in to consideration anything that has been notified over a weekend to then deduct the weekend time.

Anything received over the weekend (Saturday/Sunday) should have its lapsed days count begin at 09:00am on the following Monday morning.

Book1
	H	I	J	K	L	M	N
1	Date Notified	Day Notified	Hours	Weekend Minus	Actual Hours	Actual Hours	Date Received
2	21/11/2019 17:18:00	Thursday	24:25	00:00	24:25	1.018	22/11/2019 17:43:43
3	01/11/2019 09:18:00	Friday	0:07	00:00	0:07	0.005	01/11/2019 09:25:33
4	01/11/2019 09:50:00	Friday	0:21	00:00	0:21	0.015	01/11/2019 10:11:11
5	01/11/2019 10:17:00	Friday	0:18	00:00	0:18	0.013	01/11/2019 10:35:01
7	01/11/2019 10:09:00	Friday	0:31	00:00	0:31	0.022	01/11/2019 10:40:12
8	01/11/2019 10:42:00	Friday	0:10	00:00	0:10	0.008	01/11/2019 10:52:58
9	01/11/2019 10:41:00	Friday	0:26	00:00	0:26	0.018	01/11/2019 11:07:10
10	31/10/2019 09:49:00	Thursday	25:28	00:00	25:28	1.061	01/11/2019 11:17:22
16	01/11/2019 12:12:00	Friday	0:17	00:00	0:17	0.012	01/11/2019 12:29:12
17	31/10/2019 16:22:00	Thursday	21:02	00:00	21:02	0.877	01/11/2019 13:24:44
19	01/11/2019 13:50:00	Friday	0:19	00:00	0:19	0.014	01/11/2019 14:09:42
21	31/10/2019 10:05:00	Thursday	28:37	00:00	28:37	1.192	01/11/2019 14:42:02
22	31/10/2019 20:49:00	Thursday	18:32	00:00	18:32	0.772	01/11/2019 15:21:17
23	31/10/2019 15:59:00	Thursday	23:33	00:00	23:33	0.982	01/11/2019 15:32:30
24	01/11/2019 15:12:00	Friday	0:24	00:00	0:24	0.017	01/11/2019 15:36:02
25	31/10/2019 09:09:00	Thursday	95:50	48:00	47:50	1.994	04/11/2019 08:59:49
27	02/11/2019 15:13:00	Saturday	42:05	00:00	42:05	1.754	04/11/2019 09:18:48
30	03/11/2019 12:38:00	Sunday	20:52	00:00	20:52	0.870	04/11/2019 09:30:55
35	31/10/2019 16:38:00	Thursday	89:30	48:00	41:30	1.730	04/11/2019 10:08:53
39	03/11/2019 21:22:00	Sunday	13:30	00:00	13:30	0.563	04/11/2019 10:52:03
45	02/11/2019 08:05:00	Saturday	52:14	48:00	4:14	0.176	04/11/2019 12:19:00
46	01/11/2019 11:36:00	Friday	73:06	48:00	25:06	1.046	04/11/2019 12:42:56
48	01/11/2019 10:56:00	Friday	74:03	48:00	26:03	1.086	04/11/2019 12:59:16
49	03/11/2019 12:24:00	Sunday	25:08	00:00	25:08	1.048	04/11/2019 13:32:48
Sheet1

Cell Formulas
Range	Formula
I2:I5, I7:I10, I16:I17, I19, I21:I25, I27, I30, I35, I39, I45:I46, I48:I49	I2	=TEXT(H2, "dddd")
J2:J5, J7:J10, J16:J17, J19, J21:J25, J27, J30, J35, J39, J45:J46, J48:J49	J2	=N2-H2
K2:K5, K7:K10, K16:K17, K19, K21:K25, K27, K30, K35, K39, K45:K46, K48:K49	K2	=IF(AND(OR(I2="Thursday",I2="Friday",I2="Saturday",I2="Sunday"),AND(J2>=2)),"48:00","00:00")
L2:L5, L7:L10, L16:L17, L19, L21:L25, L27, L30, L35, L39, L45:L46, L48:L49	L2	=J2-K2

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$AA$554	I2:L5, I7:L10, I16:L17, I19:L19, I21:L25, I27:L27, I30:L30, I35:L35, I39:L39, I45:L46, I48:L49

Anyone have any ideas how to achieve this?

 

[ayeready]

Managed to get it to work using a TODAY+TIME column that i will need to update weekly. Not perfect but works.

Book1
	H	I	J	K	L	M	N
1	Date Notified	Day Notified	Weekend Modified	Hours	Weekend Minus	Actual Hours	Date Received
27	02/11/2019 15:13:00	Saturday	04/11/2019 09:00:00	0:18	00:00	0:18	04/11/2019 09:18:48
Sheet1

Cell Formulas
Range	Formula
I27	I27	=TEXT(H27, "dddd")
J27	J27	=IF(OR(I27="Saturday",I27="Sunday"),(TODAY()-28)+TIME(9,0,0),0)
K27	K27	=IF(OR(I27="Saturday",I27="Sunday"),N27-J27,N27-H27)
L27	L27	=IF(AND(OR(I27="Thursday",I27="Friday",I27="Saturday",I27="Sunday"),AND(K27>=2)),"48:00","00:00")
M27	M27	=K27-L27

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$AA$553	I27:M27