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