so close yet quite can't get in

lynzlou

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

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Do you actually need anything other than an indication of a break taken late or not at all? <b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Start</td><td style=";">break1</td><td style=";">break 2</td><td style=";">break 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Doe, John</td><td style="text-align: right;;">11:30</td><td style="text-align: right;;">14:40</td><td style="text-align: right;background-color: #FF0000;;">17:45</td><td style="text-align: right;;">19:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Doe, John</td><td style="text-align: right;;">06:30</td><td style="text-align: right;;">08:55</td><td style="text-align: right;;">11:30</td><td style="text-align: right;;">13:15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Doe, John</td><td style="text-align: right;;">13:15</td><td style="text-align: right;;">16:00</td><td style="text-align: right;;">18:45</td><td style="text-align: right;background-color: #FF0000;;">22:40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Doe, John</td><td style="text-align: right;;">13:15</td><td style="text-align: right;;">16:00</td><td style="text-align: right;;">18:45</td><td style="text-align: right;background-color: #FF0000;;">22:40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Doe, John</td><td style="text-align: right;;">06:00</td><td style="text-align: right;background-color: #FF0000;;">10:15</td><td style="text-align: right;;">11:40</td><td style="text-align: right;background-color: #FF0000;;">15:30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Doe, John</td><td style="text-align: right;;">12:30</td><td style="text-align: right;;">15:20</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Doe, John</td><td style="text-align: right;;">06:00</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">10:20</td><td style="text-align: right;;">12:40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Doe, John</td><td style="text-align: right;;">14:30</td><td style="text-align: right;;">17:00</td><td style="text-align: right;;">19:20</td><td style="text-align: right;;">22:05</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br />

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

Hope that helps.
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=OR(<font color="Blue">C2="",C2>$B2+(<font color="Red">4/24</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=OR(<font color="Blue">D2="",D2>$B2+(<font color="Red">6/24</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=OR(<font color="Blue">E2="",E2>$B2+(<font color="Red">8/24</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

lynzlou

New Member
Joined
Mar 31, 2014
Messages
15
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
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
#VALUE!





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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,952
Messages
5,525,854
Members
409,667
Latest member
jwieting

This Week's Hot Topics

Top