ChetanPuri
Board Regular
- Joined
- Sep 5, 2018
- Messages
- 63
- Office Version
- 365
- Platform
- 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.
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") |
