Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Differentiate shifts

  1. #1

    Differentiate shifts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by p45cal View Post
    Probably not.
    You will need to supply a file with your new layout and new public holiday sheet/tab.
    By the way, public holidays always start and finish at midnight?
    Public Holidays starts at midnight and finishes at midnight . I have attached the file.

    See how you can assist,
    thanks in advance

    Laurent

    Edit mod :


    https://www.excelguru.ca/forums/show...N-shifts/page2
    Attached Files Attached Files
    Last edited by Pecoflyer; 2017-06-14 at 07:18 AM.

  2. #2
    Or would be great to add a column called PH to identify all the public holidays hours falling on PHoliday dates ( 24 HR )

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,163
    Articles
    0
    Please do not hijack existing threads but create a new one for each question.
    If necessary you can add a link to an existing thread.
    I will do it for you this time
    Thank you Ken for this secure forum.

  4. #4
    Quote Originally Posted by Pecoflyer View Post
    Please do not hijack existing threads but create a new one for each question.
    If necessary you can add a link to an existing thread.
    I will do it for you this time
    Merci Beaucoup

    Apologies, appreciate your assistance, will make sure next time i won't make same mistake

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,239
    Articles
    0
    I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?

  6. #6
    Quote Originally Posted by p45cal View Post
    I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?
    its not linked to the master sheet . i just want to achieve same concept like ord, night , sat and sun , but i want a PH option added as an array based on date as per list in the PHoliday tab

  7. #7
    Quote Originally Posted by p45cal View Post
    I see in the PH sheet that you have a column A 'Applicable to'; where is the information correlating to that on the Master sheet?

    the only column linked to the PH sheet is column J which is a vlookup to get the Public Holiday name on the date

    What am trying to achieve is get results populated from column K :O where column O is P Holiday hours
    The tricky thing is to make sure when someone works from previous day night to Pholiday morning day, we need to capture the hours falling on the PH. Same principle as the night shift .

    Column E:H is part of the work done originally. What i want is a tweak -adding PH Column with will give me results as per Column KVersion 1.xlsmVersion 1.xlsm

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,239
    Articles
    0
    Quote Originally Posted by laurentdes View Post
    the only column linked to the PH sheet is column J which is a vlookup to get the Public Holiday name on the date
    and
    Quote Originally Posted by laurentdes View Post
    The tricky thing is to make sure when someone works from previous day night to Pholiday morning day, we need to capture the hours falling on the PH.
    Exactly! But different states have different holidays as is clear from the PH sheet. We will need to know the state for every row you want public holiday hours for. When working out the public holiday hours when working over midnight, in some states it might be both dates that are public holidays, in others it might be only one, in others the other, or none! If all the rows on a sheet are for the same state then we only need one cell on that sheet to tell the function which state we're in. At first sight, I haven't (so far) been able to get your current expected results without multiple states being involved.

  9. #9
    Quote Originally Posted by p45cal View Post
    and
    Exactly! But different states have different holidays as is clear from the PH sheet. We will need to know the state for every row you want public holiday hours for. When working out the public holiday hours when working over midnight, in some states it might be both dates that are public holidays, in others it might be only one, in others the other, or none! If all the rows on a sheet are for the same state then we only need one cell on that sheet to tell the function which state we're in. At first sight, I haven't (so far) been able to get your current expected results without multiple states being involved.
    Yes you are right. We need an additional Column for state. Instead of having guard number in Column, we can replace it with the State, that would be great

    Am tipping the PH will work same as a SUN shift however, the additional variables that will trigger a PH is State and the dates of PH loaded in PH tab. For the PH, may formula can be better rather than user defined function . I will keep working on it and see if i can also find a solution . Am not an expert though but i highly appreciate your help

  10. #10
    As a rule of Thumb, am not planning to work with different states at same time. Am only working on 1 State per sheet .

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •