Calculate and Identify Ordinary Hours, Double time or 1.5 Time

[ChetanPuri]

Good Afternoon Excel Team,

Just need help in creating a formula based on Start time and End time, as per the attached screenshot, any shift that finishes after 8 pm or before is 12 am classed as Afternoon Shift or after 12 am is classed as Night shift. Is there a formula that can allocate number of Hours based on the criteria as per the attached Screenshot. Many thanks, Regards, Chetan

Roster Data-26062024-28062025.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Classification	Step	Weekly pay rate	Hourly pay rate	Saturday	Sunday	Public holiday	Afternoon shift	Night shift		Employee Code	Date	Published	Role			Shift Type	Start Time	Break_Start_Time	Break Length	End Time	Shift Net Length	Ordinary	Afternoon	Saturday	Sunday
2	Level 1	1	$930.70	$24.49	$36.74	$48.98	$61.23	$27.55	$28.16		Friday	28-Jun-24	FALSE	DSW	Level 1	Step 1	Normal	16:00:00	00:00:00	0	20:00:00	4.00
3	Level 1	2	$960.70	$25.28	$37.92	$50.56	$63.20	$28.44	$29.07		Saturday	29-Jun-24	FALSE	DSW	Level 2	Step 2	Normal	16:00:00	00:00:00	0	20:00:00	4.00
4	Level 1	3	$995.00	$26.18	$39.27	$52.36	$65.45	$29.45	$30.11		Sunday	30-Jun-24	FALSE	DSW	Level 3	Step 3	Normal	16:00:00	00:00:00	0	20:00:00	4.00
5	Level 2	1	$1,223.85	$32.21	$48.32	$64.42	$80.53	$36.24	$37.04		Monday	1-Jul-24	FALSE	DSW	Level 4	Step 4	Normal	16:00:00	00:00:00	0	20:00:00	4.00
6	Level 2	2	$1,262.23	$33.22	$49.83	$66.44	$83.05	$37.37	$38.20		Tuesday	2-Jul-24	FALSE	DSW	Level 5	Step 5	Normal	16:00:00	00:00:00	0	20:00:00	4.00
7	Level 2	3	$1,300.60	$34.23	$51.35	$68.46	$85.58	$38.51	$39.36		Wednesday	3-Jul-24	FALSE	DSW	Level 6	Step 6	Normal	16:00:00	00:00:00	0	20:00:00	4.00
8	Level 2	4	$1,335.29	$35.14	$52.71	$70.28	$87.85	$39.53	$40.41		Thursday	4-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
9	Level 3	1	$1,367.86	$36.00	$54.00	$72.00	$90.00	$40.50	$41.40		Friday	5-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
10	Level 3	2	$1,407.17	$37.03	$55.55	$74.06	$92.58	$41.66	$42.58		Saturday	6-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
11	Level 3	3	$1,437.28	$37.82	$56.73	$75.64	$94.55	$42.55	$43.49		Sunday	7-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
12	Level 3	4	$1,466.77	$38.60	$57.90	$77.20	$96.50	$43.43	$44.39		Monday	8-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
13	Level 4	1	$1,577.80	$41.52	$62.28	$83.04	$103.80	$46.71	$47.75		Tuesday	9-Jul-24	FALSE	DSW			Normal	16:00:00	00:00:00	0	20:00:00	4.00
Sheet2

Cell Formulas
Range	Formula
K2:K13	K2	=TEXT(L2,"DDDD")