Ordinary Hours or Penalty Shift

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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
KLMNOPQRSTU
1Employee CodeDatePublishedRoleShift TypeStart TimeEnd TimeBreak_Start_TimeBreak LengthShift Net Length
2Friday28-Jun-24FALSEDSWNormal16:00:0020:00:0000:00:0004.00Ordinary Hours
3Saturday29-Jun-24FALSEDSWNormal16:00:0020:00:0000:00:0004.00Saturday
4Sunday30-Jun-24FALSEDSWNormal16:00:0020:00:0000:00:0004.00Sunday
5Monday1-Jul-24FALSEDSWNormal16:00:0023:59:0000:00:0004.00Afternoon Shift
6Tuesday2-Jul-24FALSEDSWNormal22:00:006:00:0000:00:0004.00Night Shift
Sheet2
Cell Formulas
RangeFormula
K2:K6K2=TEXT(L2,"DDDD")
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top