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