Ordinary Hours or Penalty Shift

[ChetanPuri]

Hi Excel Team,

Trying to build a formula to identify if a shift will be a Afternoon Shift, Night Shift or an ordinary hour shift, in column W of my worksheet. Any shift Monday to Friday 06:01 Am to 7:59 PM would be classified as Ordinary, which means I will need to first look in column K to identify which Day it is and then in column P & Q look at Start and finish time. Any help would be much appreciated.

(a) Afternoon shift means any shift which finishes after 8.00 pm and at or before 12 midnight Monday to Friday.

(b) Night shift means any shift which finishes after 12 midnight or commences before 6.00 am Monday to Friday.

(c) A public holiday shift means any time worked between midnight on the night prior to the public holiday and midnight of the public holiday.

Roster Data-26062024-28062025.xlsx
	K	L	M	N	O	P	Q	R	S	T	U
1	Employee Code	Date	Published	Role	Shift Type	Start Time	End Time	Break_Start_Time	Break Length	Shift Net Length
2	Friday	28-Jun-24	FALSE	DSW	Normal	16:00:00	20:00:00	00:00:00	0	4.00	Ordinary Hours
3	Saturday	29-Jun-24	FALSE	DSW	Normal	16:00:00	20:00:00	00:00:00	0	4.00	Saturday
4	Sunday	30-Jun-24	FALSE	DSW	Normal	16:00:00	20:00:00	00:00:00	0	4.00	Sunday
5	Monday	1-Jul-24	FALSE	DSW	Normal	16:00:00	23:59:00	00:00:00	0	4.00	Afternoon Shift
6	Tuesday	2-Jul-24	FALSE	DSW	Normal	22:00:00	6:00:00	00:00:00	0	4.00	Night Shift
Sheet2

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