formula to match Day Shift and night shift for each day of the week based on time and dates

[clh_1496]

So I have a list of Start Dates, Start Times, End dates and end times (below) , and I basically need a formula that will look up cell, and match it with the correct shift based on looking up the start and end date and then times worked - example here: 

28/05/2022

06:40

28/05/2022

08:00

Sat DS

These are the shift names, and then the times and date that make up the shift

Sat DS 6.30 - 19.00 28/05 - 28/05

Sat NS 18.40 - 7.00 28/05 - 29/05

Sun DS 6.30 - 19.00  29/05 - 29/05

Sun NS 18.40 - 7.00 29/05 - 30/05 etc

Mon DS 6.30 - 19.00 

Mon NS 18.40 - 7.00

Tues DS 6.30 - 19.00

Tues NS 18.40 - 7.00

Wed DS 6.30 - 19.00
Wed NS 18.40 - 7.00

Thurs DS 6.30 - 19.00

Thurs NS 18.40 - 7.00

Fri DS 6.30 - 19.00

Fri NS 18.40 - 7.00
Sat DS 6.30 - 19.00

Sat NS 18.40 - 7.00
Sun DS 6.30 - 19.00

Sun NS 18.40 - 7.00

28/05/2022	06:40	28/05/2022	08:00
28/05/2022	06:40	28/05/2022	08:00
28/05/2022	06:40	28/05/2022	16:15
28/05/2022	06:40	28/05/2022	18:10
28/05/2022	06:40	28/05/2022	17:10
28/05/2022	06:40	28/05/2022	19:00
28/05/2022	06:40	28/05/2022	17:10
28/05/2022	06:40	28/05/2022	17:10
28/05/2022	06:40	28/05/2022	19:00
28/05/2022	06:40	28/05/2022	17:10
28/05/2022	06:40	28/05/2022	15:10
28/05/2022	08:00	28/05/2022	16:10
28/05/2022	08:00	28/05/2022	16:10
28/05/2022	18:40	29/05/2022	07:00
28/05/2022	18:40	29/05/2022	05:10
28/05/2022	18:40	29/05/2022	07:00
28/05/2022	18:40	29/05/2022	07:00
28/05/2022	18:40	29/05/2022	07:00
28/05/2022	18:40	28/05/2022	19:40
28/05/2022	18:40	29/05/2022	05:10
28/05/2022	18:40	29/05/2022	05:10
28/05/2022	18:40	28/05/2022	19:50
28/05/2022	18:40	29/05/2022	07:00
28/05/2022	18:40	28/05/2022	19:19
28/05/2022	18:40	29/05/2022	00:40
28/05/2022	18:40	28/05/2022	23:25
28/05/2022	18:40	29/05/2022	03:10
28/05/2022	18:40	28/05/2022	19:16
28/05/2022	19:16	29/05/2022	04:10
28/05/2022	19:19	29/05/2022	05:10
28/05/2022	19:40	29/05/2022	05:10
28/05/2022	19:50	29/05/2022	07:00
28/05/2022	23:25	29/05/2022	05:10
29/05/2022	06:40	29/05/2022	16:15
29/05/2022	06:40	29/05/2022	08:00
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	07:37
29/05/2022	06:40	29/05/2022	08:40
29/05/2022	06:40	29/05/2022	15:15
29/05/2022	06:40	29/05/2022	11:55
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	07:39
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	13:53
29/05/2022	06:40	29/05/2022	11:15
29/05/2022	06:40	29/05/2022	11:21
29/05/2022	06:40	29/05/2022	08:00
29/05/2022	06:40	29/05/2022	08:00
29/05/2022	06:40	29/05/2022	08:00
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	06:40	29/05/2022	17:52
29/05/2022	06:40	29/05/2022	16:37
29/05/2022	06:40	29/05/2022	16:37
29/05/2022	06:40	29/05/2022	11:21
29/05/2022	06:40	29/05/2022	19:00
29/05/2022	07:37	29/05/2022	10:40
29/05/2022	07:39	29/05/2022	19:00
29/05/2022	08:00	29/05/2022	19:00
29/05/2022	08:00	29/05/2022	19:00
29/05/2022	08:00	29/05/2022	19:00
29/05/2022	08:00	29/05/2022	19:00
29/05/2022	08:40	29/05/2022	10:40
29/05/2022	10:40	29/05/2022	15:44
29/05/2022	10:40	29/05/2022	19:00
29/05/2022	11:15	29/05/2022	14:23
29/05/2022	11:21	29/05/2022	15:05
29/05/2022	11:21	29/05/2022	14:23
29/05/2022	11:55	29/05/2022	13:31
29/05/2022	13:31	29/05/2022	17:42
29/05/2022	13:53	29/05/2022	19:00
29/05/2022	14:23	29/05/2022	19:00
29/05/2022	14:23	29/05/2022	19:00
29/05/2022	15:05	29/05/2022	17:11
29/05/2022	15:15	29/05/2022	19:00
29/05/2022	15:44	29/05/2022	16:15
29/05/2022	16:15	29/05/2022	19:00
29/05/2022	16:15	29/05/2022	19:00
29/05/2022	16:37	29/05/2022	19:00
29/05/2022	16:37	29/05/2022	19:00
29/05/2022	17:11	29/05/2022	19:00
29/05/2022	17:42	29/05/2022	19:00
29/05/2022	17:52	29/05/2022	19:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	02:27
29/05/2022	18:40	29/05/2022	19:50
29/05/2022	18:40	29/05/2022	19:50
29/05/2022	18:40	29/05/2022	19:50
29/05/2022	18:40	29/05/2022	19:30
29/05/2022	18:40	29/05/2022	19:30
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	01:40
29/05/2022	18:40	30/05/2022	02:23
29/05/2022	18:40	29/05/2022	19:50
29/05/2022	18:40	30/05/2022	01:42
29/05/2022	18:40	29/05/2022	20:20
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	29/05/2022	22:00
29/05/2022	18:40	30/05/2022	04:43
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:40	30/05/2022	07:00
29/05/2022	18:45	29/05/2022	20:08
29/05/2022	18:45	29/05/2022	20:16
29/05/2022	18:45	30/05/2022	03:00
29/05/2022	19:30	30/05/2022	07:00
29/05/2022	19:30	30/05/2022	02:00
29/05/2022	19:50	30/05/2022	07:00
29/05/2022	19:50	30/05/2022	07:00
29/05/2022	19:50	30/05/2022	07:00
29/05/2022	19:50	30/05/2022	07:00
29/05/2022	20:08	30/05/2022	06:00
29/05/2022	20:16	29/05/2022	23:01
29/05/2022	20:20	29/05/2022	22:00
29/05/2022	22:00	30/05/2022	07:00
29/05/2022	22:00	29/05/2022	23:40
29/05/2022	23:01	30/05/2022	05:10
29/05/2022	23:40	30/05/2022	01:50
30/05/2022	01:40	30/05/2022	02:05
30/05/2022	01:42	30/05/2022	07:00
30/05/2022	01:50	30/05/2022	03:30
30/05/2022	02:00	30/05/2022	02:30
30/05/2022	02:05	30/05/2022	07:00
30/05/2022	02:23	30/05/2022	07:00
30/05/2022	02:27	30/05/2022	07:00
30/05/2022	02:30	30/05/2022	02:47
30/05/2022	02:47	30/05/2022	07:00
30/05/2022	03:30	30/05/2022	05:10
30/05/2022	04:43	30/05/2022	07:00
30/05/2022	05:10	30/05/2022	07:00
30/05/2022	06:40	30/05/2022	13:00
30/05/2022	06:40	30/05/2022	11:29
30/05/2022	06:40	30/05/2022	10:54
30/05/2022	06:40	30/05/2022	17:21
30/05/2022	06:40	30/05/2022	10:33
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	08:00
30/05/2022	06:40	30/05/2022	10:57
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	14:28
30/05/2022	06:40	30/05/2022	07:57
30/05/2022	06:40	30/05/2022	09:41
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	10:32
30/05/2022	06:40	30/05/2022	19:00
30/05/2022	06:40	30/05/2022	08:00
30/05/2022	06:40	30/05/2022	08:00
30/05/2022	07:15	30/05/2022	08:00
30/05/2022	07:15	30/05/2022	08:00
30/05/2022	07:30	30/05/2022	08:00
30/05/2022	07:30	30/05/2022	16:32
30/05/2022	07:30	30/05/2022	17:15
30/05/2022	07:30	30/05/2022	17:15
30/05/2022	07:30	30/05/2022	17:15
30/05/2022	07:30	30/05/2022	17:15
30/05/2022	07:30	30/05/2022	17:15
30/05/2022	07:57	30/05/2022	19:00
30/05/2022	08:00	30/05/2022	17:15
30/05/2022	08:00	30/05/2022	17:15
30/05/2022	08:00	30/05/2022	17:00
30/05/2022	08:00	30/05/2022	19:00
30/05/2022	08:00	30/05/2022	19:00
30/05/2022	08:00	30/05/2022	19:00
30/05/2022	09:41	30/05/2022	19:00
30/05/2022	10:32	30/05/2022	14:20
30/05/2022	10:33	30/05/2022	19:00
30/05/2022	10:54	30/05/2022	14:17
30/05/2022	10:57	30/05/2022	13:53
30/05/2022	11:29	30/05/2022	13:00
30/05/2022	13:00	30/05/2022	19:00
30/05/2022	13:00	30/05/2022	17:00
30/05/2022	13:53	30/05/2022	19:00
30/05/2022	14:17	30/05/2022	19:00
30/05/2022	14:20	30/05/2022	19:00
30/05/2022	14:28	30/05/2022	19:00
30/05/2022	16:32	30/05/2022	16:32
30/05/2022	16:32	30/05/2022	17:15
30/05/2022	17:00	30/05/2022	19:00
30/05/2022	17:21	30/05/2022	19:00
30/05/2022	18:30	31/05/2022	07:00
30/05/2022	18:40	30/05/2022	20:40
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	31/05/2022	00:40
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	31/05/2022	04:41
30/05/2022	18:40	30/05/2022	23:45
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	31/05/2022	04:30
30/05/2022	18:40	30/05/2022	20:30
30/05/2022	18:40	31/05/2022	03:48
30/05/2022	18:40	31/05/2022	02:24
30/05/2022	18:40	30/05/2022	23:40
30/05/2022	18:40	30/05/2022	19:50
30/05/2022	18:40	30/05/2022	19:50
30/05/2022	18:40	30/05/2022	23:35
30/05/2022	18:40	30/05/2022	19:50
30/05/2022	18:40	30/05/2022	19:50
30/05/2022	18:40	31/05/2022	07:00
30/05/2022	18:40	30/05/2022	22:22
30/05/2022	18:40	31/05/2022	03:27
30/05/2022	18:40	30/05/2022	20:16
30/05/2022	18:40	30/05/2022	21:30
30/05/2022	18:45	31/05/2022	07:00
30/05/2022	18:45	31/05/2022	07:00
30/05/2022	18:45	31/05/2022	07:00
30/05/2022	18:45	31/05/2022	07:00
30/05/2022	18:45	31/05/2022	07:00
30/05/2022	19:50	31/05/2022	07:00
30/05/2022	19:50	31/05/2022	07:00
30/05/2022	19:50	31/05/2022	07:00
30/05/2022	19:50	31/05/2022	07:00
30/05/2022	20:16	31/05/2022	02:43
30/05/2022	20:30	31/05/2022	07:00
30/05/2022	20:40	30/05/2022	22:58
30/05/2022	21:15	31/05/2022	07:00
30/05/2022	21:30	31/05/2022	07:00
30/05/2022	22:22	30/05/2022	22:29
30/05/2022	22:29	31/05/2022	01:24
30/05/2022	22:58	31/05/2022	07:00
30/05/2022	23:35	31/05/2022	07:00
30/05/2022	23:40	31/05/2022	07:00
30/05/2022	23:45	31/05/2022	07:00
31/05/2022	01:24	31/05/2022	07:00
31/05/2022	02:24	31/05/2022	03:39
31/05/2022	02:43	31/05/2022	07:00
31/05/2022	03:27	31/05/2022	07:00
31/05/2022	03:48	31/05/2022	07:00
31/05/2022	04:30	31/05/2022	07:00
31/05/2022	04:41	31/05/2022	07:00
31/05/2022	06:40	31/05/2022	08:18
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	07:50
31/05/2022	06:40	31/05/2022	10:41
31/05/2022	06:40	31/05/2022	09:05
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	08:18
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	08:23
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	06:40	31/05/2022	11:14
31/05/2022	06:40	31/05/2022	08:00
31/05/2022	06:40	31/05/2022	19:00
31/05/2022	07:15	31/05/2022	08:15
31/05/2022	07:15	31/05/2022	08:15
31/05/2022	07:30	31/05/2022	17:15
31/05/2022	07:30	31/05/2022	11:29
31/05/2022	07:30	31/05/2022	17:15
31/05/2022	07:30	31/05/2022	17:15
31/05/2022	07:30	31/05/2022	17:15
31/05/2022	07:30	31/05/2022	17:15
31/05/2022	07:30	31/05/2022	11:14
31/05/2022	07:30	31/05/2022	07:54
31/05/2022	07:30	31/05/2022	08:30
31/05/2022	07:30	31/05/2022	14:41
31/05/2022	07:50	31/05/2022	19:00
31/05/2022	07:54	31/05/2022	12:04
31/05/2022	08:15	31/05/2022	17:00
31/05/2022	08:15	31/05/2022	17:00
31/05/2022	08:18	31/05/2022	19:00
31/05/2022	08:18	31/05/2022	19:00
31/05/2022	08:23	31/05/2022	19:00
31/05/2022	08:30	31/05/2022	17:15
31/05/2022	09:05	31/05/2022	17:10
31/05/2022	10:41	31/05/2022	13:01
31/05/2022	11:14	31/05/2022	11:14
31/05/2022	11:14	31/05/2022	17:15
31/05/2022	11:14	31/05/2022	19:00
31/05/2022	11:29	31/05/2022	17:15
31/05/2022	12:04	31/05/2022	17:15
31/05/2022	13:01	31/05/2022	19:00
31/05/2022	14:41	01/06/2022	07:56
31/05/2022	17:10	31/05/2022	19:00
31/05/2022	18:30	01/06/2022	06:41
31/05/2022	18:40	01/06/2022	06:40
31/05/2022	18:40	31/05/2022	23:04
31/05/2022	18:40	31/05/2022	19:50
31/05/2022	18:40	31/05/2022	19:50
31/05/2022	18:40	31/05/2022	19:50
31/05/2022	18:40	01/06/2022	06:41
31/05/2022	18:40	01/06/2022	06:39
31/05/2022	18:40	01/06/2022	06:40
31/05/2022	18:40	01/06/2022	06:39
31/05/2022	18:40	01/06/2022	07:00
31/05/2022	18:40	01/06/2022	07:00
31/05/2022	18:40	31/05/2022	22:34
31/05/2022	18:40	31/05/2022	20:14
31/05/2022	18:40	01/06/2022	02:09
31/05/2022	18:40	01/06/2022	01:05
31/05/2022	18:40	01/06/2022	06:40
31/05/2022	18:40	01/06/2022	02:57
31/05/2022	18:40	01/06/2022	06:40
31/05/2022	18:40	01/06/2022	06:41
31/05/2022	18:40	01/06/2022	06:39
31/05/2022	18:40	31/05/2022	21:15
31/05/2022	18:45	01/06/2022	03:21
31/05/2022	18:45	01/06/2022	03:21
31/05/2022	18:45	01/06/2022	07:00
31/05/2022	19:50	01/06/2022	06:40
31/05/2022	19:50	01/06/2022	06:40
31/05/2022	19:50	01/06/2022	00:40
31/05/2022	20:14	31/05/2022	22:48
31/05/2022	21:15	01/06/2022	06:24
31/05/2022	21:15	01/06/2022	07:00
31/05/2022	21:54	01/06/2022	06:40
31/05/2022	22:00	01/06/2022	02:00
31/05/2022	22:34	01/06/2022	07:00
31/05/2022	22:48	01/06/2022	03:21
31/05/2022	23:04	01/06/2022	03:47
01/06/2022	01:05	01/06/2022	03:15
01/06/2022	02:00	01/06/2022	04:00
01/06/2022	02:09	01/06/2022	06:40
01/06/2022	02:57	01/06/2022	06:39
01/06/2022	03:15	01/06/2022	03:30
01/06/2022	03:21	01/06/2022	07:00
01/06/2022	03:21	01/06/2022	07:00
01/06/2022	03:21	01/06/2022	03:58
01/06/2022	03:30	01/06/2022	07:00
01/06/2022	03:47	01/06/2022	07:00
01/06/2022	03:58	01/06/2022	05:44
01/06/2022	04:00	01/06/2022	05:00
01/06/2022	05:44	01/06/2022	07:00
01/06/2022	06:40	00/01/1900	00:00
01/06/2022	06:40	01/06/2022	09:45
01/06/2022	06:40	00/01/1900	00:00
01/06/2022	06:57	00/01/1900	00:00
01/06/2022	07:00	00/01/1900	00:00
01/06/2022	07:00	00/01/1900	00:00
01/06/2022	07:00	01/06/2022	07:00
01/06/2022	07:00	00/01/1900	00:00
01/06/2022	07:01	01/06/2022	07:01
01/06/2022	07:01	00/01/1900	00:00
01/06/2022	07:02	00/01/1900	00:00
01/06/2022	07:06	01/06/2022	07:06
01/06/2022	07:06	00/01/1900	00:00
01/06/2022	07:06	00/01/1900	00:00
01/06/2022	07:16	01/06/2022	07:16
01/06/2022	07:16	00/01/1900	00:00
01/06/2022	07:16	01/06/2022	07:16
01/06/2022	07:16	00/01/1900	00:00
01/06/2022	07:24	00/01/1900	00:00
01/06/2022	07:24	01/06/2022	07:24
01/06/2022	07:32	01/06/2022	07:32
01/06/2022	07:32	00/01/1900	00:00
01/06/2022	07:45	00/01/1900	00:00
01/06/2022	07:46	00/01/1900	00:00
01/06/2022	07:47	00/01/1900	00:00
01/06/2022	07:47	00/01/1900	00:00
01/06/2022	07:56	01/06/2022	09:22
01/06/2022	07:59	00/01/1900	00:00
01/06/2022	08:14	00/01/1900	00:00
01/06/2022	08:36	01/06/2022	10:20
01/06/2022	09:22	00/01/1900	00:00
01/06/2022	09:32	00/01/1900	00:00
01/06/2022	09:45	00/01/1900	00:00
01/06/2022	10:20	00/01/1900	00:00
01/06/2022	10:59	00/01/1900	00:00

[#2 · Accepted solution]

@clh_1496 Quite a few odd dates towards the end in your list (start Jun 1 2022, end 0 January 1900. So let's ignore those. Perhaps the picture below helps you resolve your issue. If not, please clarify.

[#3]

@Riny_van_Eekelen 

That's just because they're blank as the guys in the shift haven't clocked off yet so its entered a error time in. 

This formula works for almost all of them, but for some reason, there's a period in the early hours where it reads 30/05/2022 01.40 as Mon NS rather a continuation of Sun NS - assuming this is because it reads the date?

Sun NS	29/05/2022	20:16	29/05/2022
Sun NS	29/05/2022	20:20	29/05/2022
Sun NS	29/05/2022	22:00	30/05/2022
Sun NS	29/05/2022	22:00	29/05/2022
Sun NS	29/05/2022	23:01	30/05/2022
Sun NS	29/05/2022	23:40	30/05/2022
Mon NS	30/05/2022	01:40	30/05/2022
Mon NS	30/05/2022	01:42	30/05/2022
Mon NS	30/05/2022	01:50	30/05/2022
Mon NS	30/05/2022	02:00	30/05/2022
Mon NS	30/05/2022	02:05	30/05/2022
Mon NS	30/05/2022	02:23	30/05/2022
Mon NS	30/05/2022	02:27	30/05/2022
Mon NS	30/05/2022	02:30	30/05/2022
Mon NS	30/05/2022	02:47	30/05/2022
Mon NS	30/05/2022	03:30	30/05/2022
Mon NS	30/05/2022	04:43	30/05/2022
Mon NS	30/05/2022	05:10	30/05/2022
Mon DS	30/05/2022	06:40	30/05/2022

[#4]

I've managed to make a small adjustment to the formula you provided and it works!! Thank you for your help!!

=IF(AND(V142>='Reference Sheet'!$C$14,V142<='Reference Sheet'!$C$12),TEXT(U142-1,"ddd")&" "&IF(AND(V142>='Reference Sheet'!$C$12,V142<'Reference Sheet'!$C$13),'Reference Sheet'!$D$12,'Reference Sheet'!$D$13),TEXT(U142,"ddd")&" "&IF(AND(V142>='Reference Sheet'!$C$12,V142<'Reference Sheet'!$C$13),'Reference Sheet'!$D$12,'Reference Sheet'!$D$13))