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

1. ## 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. ## 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))

3. ## 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?

4. ## Re: so close yet quite can't get in

Originally Posted by lynzlou
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

 A B C D E 1 Name Start break1 break 2 break 3 2 Doe, John 11:30 14:40 17:45 19:00 3 Doe, John 6:30 8:55 11:30 13:15 4 Doe, John 13:15 16:00 18:45 22:40 5 Doe, John 13:15 16:00 18:45 22:40 6 Doe, John 6:00 10:15 11:40 15:30 7 Doe, John 12:30 15:20 8 Doe, John 6:00 8:00 10:20 12:40 9 Doe, John 14:30 17:00 19:20 22:05 10 SAHAR, SAKHI AHMAD 11:00 13:00 17:05 18:30 11 ALCE, KEVIN-ELLIOTT 4:00 6:10 8:05 11:30

Conditional formatting
 Cell Nr.: / Condition Format C2 1. / Formula is =OR(C2="",C2>\$B2+(4/24)) Abc D2 1. / Formula is =OR(D2="",D2>\$B2+(6/24)) Abc E2 1. / Formula is =OR(E2="",E2>\$B2+(8/24)) Abc

Excel tables to the web >> Excel Jeanie HTML 4

Originally Posted by lynzlou
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.