so close yet quite can't get in

lynzlou

New Member
Joined
Mar 31, 2014
Messages
16
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?!?!?

NameStartbreak1break 2break 3
Doe, John11:3014:4017:4519:00
Doe, John06:3008:5511:3013:15
Doe, John13:1516:0018:4522:40
Doe, John13:1516:0018:4522:40
Doe, John06:0010:1511:4015:30
Doe, John12:3015:20
Doe, John06:0008:0010:2012:40
Doe, John14:3017:0019:2022:05
<colgroup><col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5412;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" span="4"> <tbody> </tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
Cell Formulas
RangeFormula
H2=OR(C2="",C2>$B2+(4/24))
I2=OR(D2="",D2>$B2+(6/24))
J2=OR(E2="",E2>$B2+(8/24))
 
Upvote 0
I thought we had it but its not calculating correctly in some cells and I am stumped as to why!



SAHAR, SAKHI AHMAD11:0013:0017:0518:30

<tbody>
</tbody>


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-ELLIOTT4:006:009:2511:30

<tbody>
</tbody>

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

but it is working for other entries
ANSAR, SYED4:006:108:0511:30

<tbody>
</tbody>


I am seriously confused :( is there a way to attach my spreadsheet so it makes more sense to look at?
 
Last edited:
Upvote 0
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.

Excel Workbook
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
Incorrect break times
Cells with Conditional Formatting
CellConditionCell FormatStop If True
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





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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top