Page 2 of 2 FirstFirst 1 2
Results 11 to 17 of 17

Thread: Differentiate shifts

  1. #11


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

    i have attached the workings i did, column M: P are the hours that are PH . instead of having them in 4 columns, i want to add another column after Column H Sun and name it PH . The PH column will have the total hours from M:P .
    Attached Files Attached Files

  2. #12
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,239
    Articles
    0
    In the attached:

    • Columns Q:T of Master sheet is your manually created expected results
    • Columns K:N my results (conditionally formatted to highlight differences)
    • Columns V:Y a comparison of my results versus your expected results (conditionally formatted to highlight differences)
    • Column O is the state applicable to each row to get your results
    • Column B of the PH sheet has highlights where you had some unusual dates
    • I've altered the format of column B of the Master sheet to show days of the week (only to help visual checking of results)


    I have made alterations to the existing ShiftSplit user-defined function:
    There is no change to how it's used in columns E:H of the Master sheet.
    Here's how it's used in columns K:N:
    =IFERROR(ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,O10)*24,"")

    Singling out the function itself in that formula:
    ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,O10)
    you can see an additional 2 arguments;

    • the first new argument being the 2-column range containing the dates and states (in that order),
    • the second new argument being the state for that row.


    The second new argument (O10) could be a literal string eg.:
    ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,"ACT")
    or a reference to a single cell containig the state for the sheet eg.:
    ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,$J$1)

    These last 2 new arguments are optional; their very presence indicates that the results will be the public holiday aspect of the working hours.

    The formula is, as before, an array-entered one, 4 cells across entered in one go.

    So far I haven't streamlined the code in the function; if I get time/inclination I will do this.
    So now it's over to you to test thoroughly.

    There is an outstanding difference between your expected results and mine on row 112; I think that your expected results are awry (you can't expect Sunday public holiday hours on working overnight from Thursday to Friday!).
    Attached Files Attached Files
    Last edited by p45cal; 2017-06-19 at 12:20 PM.

  3. #13
    Hi

    thanks for everything . Line 112 was my boubou . you were right , i def got it wrong
    I have done a few testings and so far so good. Am very happy with the expected results . Its helpful and i can tell that you have done a great job .

    Cheers
    Last edited by p45cal; 2017-06-20 at 12:31 AM. Reason: removed wholesale quoting

  4. #14
    Hi When i input times 15:00 to 23:00 , it doesnt detect the PH hours on PH days .Just an example

    Regards

  5. #15
    Quote Originally Posted by laurentdes View Post
    Hi

    thanks for everything . Line 112 was my boubou . you were right , i def got it wrong
    I have done a few testings and so far so good. Am very happy with the expected results . Its helpful and i can tell that you have done a great job .

    Cheers
    When end times falls next daY IT works fine, but when end time falls same day its not picking the hours as PH

  6. #16
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,239
    Articles
    0
    I've only had a quick glance and made a small change; could you test the attached again?
    Attached Files Attached Files

  7. #17
    Quote Originally Posted by p45cal View Post
    I've only had a quick glance and made a small change; could you test the attached again?
    Sounds good, will do further testing
    Nice Work thx

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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