+ Reply to Thread
Results 1 to 5 of 5

Rostered breaks

Hybrid View

drasston369 Rostered breaks 02-25-2016, 06:45 PM
protonLeah Re: Rostered breaks 02-25-2016, 09:02 PM
drasston369 Re: Rostered breaks 02-25-2016, 09:23 PM
protonLeah Re: Rostered breaks 02-27-2016, 01:08 AM
drasston369 Re: Rostered breaks 02-28-2016, 05:36 PM
  1. #1
    Registered User
    Join Date
    02-25-2016
    Location
    CHCH
    MS-Off Ver
    2013
    Posts
    6

    Rostered breaks

    I have a roster.
    And I currently use a formula that takes a half hour break out of the worked hours, but also inputs the data as blank if S/L, A/L,STAT or -. is entered into the shift boxes.

    What would be fantastic is if it only calculated the hour hour break if the shift is more than 5 hrs long, and calculates an hour break if the shift is more than 10hrs long.

    So if the shift is only 4hrs long then there would be no break subtracted.

    I shall attach an example workbook.

    If theres any feedback on making the formulas simpler or if i missed an easy way to do this please let me know.roster snapshot.pngroster snapshot.png
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,937

    Re: Rostered breaks

    J5 (typical):
    =IF(ISERROR(SEARCH(H5:I5,"-A/LS/LRDO")),(I5-H5)*24-IF(I5-H5>=10/24,1,IF(I5-H5>=5/24,0.5,0)),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-25-2016
    Location
    CHCH
    MS-Off Ver
    2013
    Posts
    6

    Re: Rostered breaks

    That works almost perfectly, but on shifts over 10hours long I need it to deduct an hour break not half an hour.

    Is there a way, perhaps in a separate cell(to determine on floor selling hours), to deduct a further half hour if the shift begins at 8:30am as he first half hour would be store setup.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,937

    Re: Rostered breaks

    Modified to account for starting at 8:30
    =IF(ISERROR(SEARCH(H5:I5,"-A/LS/LRDO")),(I5-H5)*24-IF(I5-H5>=10/24,1,IF(I5-H5>=5/24,0.5,0))-IF(H5=8.5/24,0.5,0),"")
    If you only want to subtract one hour for times over 10 hours but not including 10 then use
    I5-H5>10/24
    Last edited by protonLeah; 02-28-2016 at 12:49 AM.

  5. #5
    Registered User
    Join Date
    02-25-2016
    Location
    CHCH
    MS-Off Ver
    2013
    Posts
    6

    Re: Rostered breaks

    Brilliant! Thanks so much for the help guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to create a formula to work out rostered hours
    By Macka2626 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-28-2015, 06:34 PM
  2. [SOLVED] I need to return names from a work roster list based on their rostered days on/off
    By notthatgoodwithexcel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-31-2015, 09:50 AM
  3. Clear all page breaks, then insert new breaks at change in data
    By Alex0929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2014, 03:12 PM
  4. calculating rostered days off
    By Xtopher in forum Excel General
    Replies: 5
    Last Post: 03-05-2010, 01:20 AM
  5. Calculating Rostered Hours
    By flebber in forum Excel General
    Replies: 10
    Last Post: 01-14-2010, 12:38 AM
  6. Hard page breaks being replaced by soft breaks in the wrong place
    By JDavies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2008, 10:28 AM
  7. Hard breaks in text to soft breaks in Excel
    By tbailey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2005, 12:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1