Shift schedules against week number

[silambarasan.J]

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

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

---

Your workbook has no formulas, etc to indicate how you tried to solve your problem???

---

Want to get your question answered quickly?

• Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers.
• On the other hand, skip irrelevant details. Be descriptive and concise. Short, direct, and to-the-point questions with apt thread titles are almost always answered promptly.
• Keep the scope reasonably narrow. Questions like, "How do I set up an accounting system in Excel?" might be a long time waiting.
• Explain what you've already tried. ("Calculation is set to automatic, but formulas still don't compute") so helpers don't waste your time or theirs.
• Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

***
Go to the Commercial Services section of this site http://www.excelforum.com/commercial-services/
And read the FAQ on how to solicit paid help.
***