Team on Shift

[NZAS]

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 Stamp	Team 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

 

[NZAS]

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

 

[CsJHUN]

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
	AL	AM	AN	AO	AP	AQ	AR	AS	AT
2	2020.09.30
3
4	0	AD	A	D		2020.10.15	DC	2020.10.15 9:00	C
5	1	AD	A	D		2020.10.16	AD	2020.10.15 21:00	D
6	2	BA	B	A		2020.10.17	AD	2020.10.16 9:00	C
7	3	BA	B	A		2020.10.18	AD	2020.10.16 21:00	D
8	4	CB	C	B		2020.10.19	AD	2020.10.17 9:00	C
9	5	CB	C	B		2020.10.20	AD	2020.10.17 21:00	C
10	6	DC	D	C		2020.10.21	AD	2020.10.18 9:00	B
11	7	DC	D	C		2020.10.22	AD	2020.10.18 21:00	C
12						2020.10.23	AD	2020.10.19 9:00	B
13						2020.10.24	AD	2020.10.19 21:00	B
14						2020.10.25	AD	2020.10.20 9:00	A
15						2020.10.26	BA	2020.10.20 21:00	B
16						2020.10.27	BA	2020.10.21 9:00	B
17						2020.10.28	CB	2020.10.21 21:00	C
18								2020.10.22 9:00	B
19								2020.10.22 21:00	C
20								2020.10.23 9:00	C
21								2020.10.23 21:00	D
22								2020.10.24 9:00	C
23								2020.10.24 21:00	D
24								2020.10.25 9:00	D
Munka1

Cell Formulas
Range	Formula
AT4:AT24	AT4	=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:AS24	AS5	=AS4+0.5
AR4:AR17	AR4	=VLOOKUP(MOD(AQ4-AL2,8),$AL$4:$AM$11,2,0)