Shift Pattern Adding Hours based on Shift etc

**fgbuk** · 08-01-2016, 12:38 PM

Hello I have various shifts, I have a spread sheet which is CSV ready for google and outlook calendar ready, to save time I wish to automate some of the stuff i.e. dependant on what is inputted in Cell A** (about four five variables ET, LT, NT, AL, RD) I want cells C D E completed accordingly.
ET Early shift 8 Hours so C - 07:00 D Date of Fin E time of finish in this case 15:00
LT Late Shift 9 Hours so C - 14:00 D Date of Fin E time of fin in this case 23:00
NT Night Shift 9 Hours so C - 22:00 D Date of fin in this case following days date, E time of finish in this case 07:00 following day
RD Rest day no value leave blank count nothing
AL Annual leave no value leave blank count nothing
I have tried to attach an image but not sure if its been successful, happy to send image of screen shot
I have tried IF TIME etc statement not getting anywhere
But can anyone help ?
thanks

**AliGW** · 08-01-2016, 12:40 PM

Rather than an image, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**JeteMc** · 08-01-2016, 07:56 PM

This solution is tested for the 2010 version however I believe that it will work for the 2007 version. The formulas for columns C and E are standard IF's respectively being:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The formula for column D you'll need to change the iterative calculation settings so that you can create a timestamp:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Shift Pattern Adding Hours based on Shift etc

LinkBack

Thread Tools

Rate This Thread

Display

Shift Pattern Adding Hours based on Shift etc

Re: Shift Pattern Adding Hours based on Shift etc

Re: Shift Pattern Adding Hours based on Shift etc

Thread Information

Users Browsing this Thread

Similar Threads

Need a formula to calculate time elapsed within work shift based on clocked in hours

Table that matches work shift hours to work shift codes

Shift Pattern Help

[SOLVED] Spliting Day Shift and Night Shift Hours

Calculating what shift should be running on a three week , three shift rotating pattern

Calculate night shift hours worked - working hours span the night shift start and end

Calculating Hours Worked from Shift Begin and Shift End

Bookmarks

Bookmarks

Posting Permissions