Shift schedules against week number

Hi

Column B is date and column C is a week number.

• Week starts on odd number are late shift - working hours 14:00-23:15
• During the late shifts every Saturday are day off and Sunday is working day working hours 07:00-14:00
• Week starts on even number are early shift – working hours 06:00-14:15
• During Early shifts Saturday’s and Sunday’s are working day – working hours 07:00-14:00

I have 3 Query’s.

1) To work out Shifts according to week numbers (early or Late)
2) Shift timing according to shifts
3) To Calculate Working hours from (2)

Please help me. Herewith attached sample workbook


DAY DATE WEEK SHIFT TIME WORKING HOURS

Monday 20/03/2017 51 Late 15:00-23:15 8
Tuesday 21/03/2017 51 Late 15:00-23:15 8
Wednesday 22/03/2017 51 Late 15:00-23:15 8
Thursday 23/03/2017 51 Late 15:00-23:15 8
Friday 24/03/2017 51 Late 15:00-23:15 8
Saturday 25/03/2017 51 Week End Off -
Sunday 26/03/2017 51 Week End 07:00-14:00 7
Monday 27/03/2017 52 Early 06:00-14:15 8
Tuesday 28/03/2017 52 Early 06:00-14:15 8
Wednesday 29/03/2017 52 Early 06:00-14:15 8
Thursday 30/03/2017 52 Early 06:00-14:15 8
Friday 31/03/2017 52 Early 06:00-14:15 8
Saturday 01/04/2017 52 Week End 07:00-14:00 7
Sunday 02/04/2017 52 Week End 07:00-14:00 7
Monday 03/04/2017 1 Late 15:00-23:15 8
Tuesday 04/04/2017 1 Late 15:00-23:15 8
Wednesday 05/04/2017 1 Late 15:00-23:15 8
Thursday 06/04/2017 1 Late 15:00-23:15 8
Friday 07/04/2017 1 Late 15:00-23:15 8
Saturday 08/04/2017 1 Week End Off -
Sunday 09/04/2017 1 Week End 07:00-14:00 7
Monday 10/04/2017 2 Early 06:00-14:15 8
Tuesday 11/04/2017 2 Early 06:00-14:15 8
Wednesday 12/04/2017 2 Early 06:00-14:15 8
Thursday 13/04/2017 2 Early 06:00-14:15 8
Friday 14/04/2017 2 Early 06:00-14:15 8
Saturday 15/04/2017 2 Week End 07:00-14:00 7
Sunday 16/04/2017 2 Week End 07:00-14:00 7