Results 1 to 4 of 4

Thread: so close yet quite can't get in
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default so close yet quite can't get in

    Hello! I feel like I am sooo close to figuring out this formula or conditional formatting but just can't quite get there. Its simple... per a collective agreement, any of employees that take their breaks "late" or don't take them at all are entitled to pay at 1.5x for that break. I get an excel sheet everyday that shows me their start time, when they took first break, second break and third break. Breaks should be taken as follows:

    Break 1 - start break between 2 and 4 hours from their start time (so if they start at 1100 break one must start between 1300 an 1500hrs
    Break 2 - start betwee hour 4 and 6 from start time
    Break 3- start between hour and 8 from start time

    **all calculated on shift start time - so even if break 1 was late, breaks 2 3might be OK

    surely there must be way to highlight the cell if break start is not within these ranges?? I managed to figure out if break starts more than 2,4 or 6 hours but not in a range.

    help?!?!?

    Name Start break1 break 2 break 3
    Doe, John 11:30 14:40 17:45 19:00
    Doe, John 06:30 08:55 11:30 13:15
    Doe, John 13:15 16:00 18:45 22:40
    Doe, John 13:15 16:00 18:45 22:40
    Doe, John 06:00 10:15 11:40 15:30
    Doe, John 12:30 15:20
    Doe, John 06:00 08:00 10:20 12:40
    Doe, John 14:30 17:00 19:20 22:05

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: so close yet quite can't get in

    Do you actually need anything other than an indication of a break taken late or not at all? Excel 2010
    ABCDE
    1NameStartbreak1break 2break 3
    2Doe, John11:3014:4017:4519:00
    3Doe, John06:3008:5511:3013:15
    4Doe, John13:1516:0018:4522:40
    5Doe, John13:1516:0018:4522:40
    6Doe, John06:0010:1511:4015:30
    7Doe, John12:3015:20
    8Doe, John06:0008:0010:2012:40
    9Doe, John14:3017:0019:2022:05

    Sheet4





    Blow, row 2 shows formulas aplied to columns C, D, E to give the conditional highlighting.

    Hope that helps.
    Excel 2010
    HIJ
    2FALSETRUEFALSE
    3FALSEFALSEFALSE
    4FALSEFALSETRUE
    5FALSEFALSETRUE
    6TRUEFALSETRUE
    7FALSETRUETRUE
    8FALSEFALSEFALSE
    9FALSEFALSEFALSE

    Sheet4



    Worksheet Formulas
    CellFormula
    H2=OR(C2="",C2>$B2+(4/24))
    I2=OR(D2="",D2>$B2+(6/24))
    J2=OR(E2="",E2>$B2+(8/24))

    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: so close yet quite can't get in

    I thought we had it but its not calculating correctly in some cells and I am stumped as to why!



    SAHAR, SAKHI AHMAD 11:00 13:00 17:05 18:30


    in this example:
    1100 is start time
    1300 is start of Break 1 (rule: start between 1300 and 1500)
    1705 is start of Break 2 (rule: start between 1500 and 1700)
    1830 is start of Break 3 (rule: start between 1700 and 1900)

    the formulas calculated Column D & E as red (I made them bold to indicate anything that turned yet - not sure why its not pasting) but Break 3 can start anytime between 1700 (5pm) and 1900 (7pm) and started at 1830 (6:30pm) so not sure why its red???

    ALCE, KEVIN-ELLIOTT 4:00 6:00 9:25 11:30

    same here second break could be between 0800-1000 and third break between 10am-12pm

    but it is working for other entries
    ANSAR, SYED 4:00 6:10 8:05 11:30


    I am seriously confused is there a way to attach my spreadsheet so it makes more sense to look at?
    Last edited by lynzlou; Aug 23rd, 2019 at 11:04 PM.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: so close yet quite can't get in

    Quote Originally Posted by lynzlou View Post
    I thought we had it but its not calculating correctly in some cells and I am stumped as to why!
    As far as I understand what you want, the suggested formulas do return the correct results for me. Here they are. I suspect that you may have (perhaps accidentally) applied the CF formula from the break1 column to the other two break columns as well instead of using the separate formulas that Snakehips gave you.

    Incorrect break times

    ABCDE
    1NameStartbreak1break 2break 3
    2Doe, John11:3014:4017:4519:00
    3Doe, John6:308:5511:3013:15
    4Doe, John13:1516:0018:4522:40
    5Doe, John13:1516:0018:4522:40
    6Doe, John6:0010:1511:4015:30
    7Doe, John12:3015:20
    8Doe, John6:008:0010:2012:40
    9Doe, John14:3017:0019:2022:05
    10SAHAR, SAKHI AHMAD11:0013:0017:0518:30
    11ALCE, KEVIN-ELLIOTT4:006:108:0511:30

    Conditional formatting
    CellNr.: / ConditionFormat
    C21. / Formula is =OR(C2="",C2>$B2+(4/24))Abc
    D21. / Formula is =OR(D2="",D2>$B2+(6/24))Abc
    E21. / Formula is =OR(E2="",E2>$B2+(8/24))Abc


    Excel tables to the web >> Excel Jeanie HTML 4




    Quote Originally Posted by lynzlou View Post
    is there a way to attach my spreadsheet so it makes more sense to look at?
    The forum does not allow attachments of actual sheets but you can show what is going on in your sheet with small screen shots that can include information like columns/rows/ formatting etc as both Snakehips and I have done. There is a link in my signature block beolow with help.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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