Team on Shift

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I would like a formula that would use the date time in Column A that would give me the Team on duty rather than me manually adding the team.
There are 4 teams working a rotation of 12 hr shifts 08:00 to 20:00 is dayshift and 20:00 to 08:00 next day is nightshift.
The example below the actual rotation for some date time stamps as an example. for the formula If a time stamp falls between the shift times then give the team name that would be working that shift
The Date time stamp in column A could be any time of the day or night
This is the rotation of the teams if starting on the 9/10/2020 @ 08:00:00 to 20:00:00 which would be Team 1 working from 08:00:00 to 20:00:00 and Team 4 working from 20:00:00 to 08:00:00 the next day

Cheer
NZAS
Date Time StampTeam working at the time
9/10/2020 9:15​
Team 1
9/10/2020 21:15​
Team 4
10/10/2020 9:15​
Team 1
10/10/2020 21:15​
Team 4
11/10/2020 9:15​
Team 2
11/10/2020 21:15​
Team 1
12/10/2020 9:15​
Team 2
12/10/2020 21:15​
Team 1
13/10/2020 9:15​
Team 3
13/10/2020 21:15​
Team 2
14/10/2020 9:15​
Team 3
14/10/2020 21:15​
Team 2
15/10/2020 9:15​
Team 4
15/10/2020 21:15​
Team 3
16/10/2020 9:15​
Team 4
16/10/2020 21:15​
Team 3
17/10/2020 9:15​
Team 1
17/10/2020 21:15​
Team 4
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I forgot to add this is the teams for October 2020 for the shifts I have the rest of the year if needed to see the teams for shifts
1601944692190.png
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
356
Office Version
  1. 2016
Platform
  1. Windows
Hi NZAS, i edited my 3/3 12h shift schedule method.
You will need the AL:AQ data and the formula in AT

Munkafüzet1
ALAMANAOAPAQARASAT
22020.09.30
3
40ADAD2020.10.15DC2020.10.15 9:00C
51ADAD2020.10.16AD2020.10.15 21:00D
62BABA2020.10.17AD2020.10.16 9:00C
73BABA2020.10.18AD2020.10.16 21:00D
84CBCB2020.10.19AD2020.10.17 9:00C
95CBCB2020.10.20AD2020.10.17 21:00C
106DCDC2020.10.21AD2020.10.18 9:00B
117DCDC2020.10.22AD2020.10.18 21:00C
122020.10.23AD2020.10.19 9:00B
132020.10.24AD2020.10.19 21:00B
142020.10.25AD2020.10.20 9:00A
152020.10.26BA2020.10.20 21:00B
162020.10.27BA2020.10.21 9:00B
172020.10.28CB2020.10.21 21:00C
182020.10.22 9:00B
192020.10.22 21:00C
202020.10.23 9:00C
212020.10.23 21:00D
222020.10.24 9:00C
232020.10.24 21:00D
242020.10.25 9:00D
Munka1
Cell Formulas
RangeFormula
AT4:AT24AT4=VLOOKUP(MOD(IF(HOUR(AS4<8),ROUNDDOWN(AS4,0),ROUNDDOWN(AS4,0)-1)-AL2,8),$AL$4:$AO$11,IF(OR(HOUR(AS4)<8,HOUR(AS4)>20),3,4),0)
AS5:AS24AS5=AS4+0.5
AR4:AR17AR4=VLOOKUP(MOD(AQ4-AL2,8),$AL$4:$AM$11,2,0)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,396
Members
414,448
Latest member
Jessica 22664

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
Top