so close yet quite can't get in

[lynzlou]

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?!?!?

[TABLE="width: 419"]
<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>[TR]
[TD="width: 148, bgcolor: transparent"]Name[/TD]
[TD="width: 102, bgcolor: transparent"]Start[/TD]
[TD="width: 102, bgcolor: transparent"]break1[/TD]
[TD="width: 102, bgcolor: transparent"]break 2[/TD]
[TD="width: 102, bgcolor: transparent"]break 3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]11:30[/TD]
[TD="bgcolor: transparent"]14:40[/TD]
[TD="bgcolor: transparent"]17:45[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]06:30[/TD]
[TD="bgcolor: transparent"]08:55[/TD]
[TD="bgcolor: transparent"]11:30[/TD]
[TD="bgcolor: transparent"]13:15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]13:15[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent"]18:45[/TD]
[TD="bgcolor: transparent"]22:40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]13:15[/TD]
[TD="bgcolor: transparent"]16:00[/TD]
[TD="bgcolor: transparent"]18:45[/TD]
[TD="bgcolor: transparent"]22:40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]06:00[/TD]
[TD="bgcolor: transparent"]10:15[/TD]
[TD="bgcolor: transparent"]11:40[/TD]
[TD="bgcolor: transparent"]15:30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]12:30[/TD]
[TD="bgcolor: transparent"]15:20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]06:00[/TD]
[TD="bgcolor: transparent"]08:00[/TD]
[TD="bgcolor: transparent"]10:20[/TD]
[TD="bgcolor: transparent"]12:40
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Doe, John[/TD]
[TD="bgcolor: transparent"]14:30[/TD]
[TD="bgcolor: transparent"]17:00[/TD]
[TD="bgcolor: transparent"]19:20[/TD]
[TD="bgcolor: transparent"]22:05[/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

Do you actually need anything other than an indication of a break taken late or not at all?

Excel 2010
	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	06:30	08: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	06:00	10:15	11:40	15:30
7	Doe, John	12:30	15:20
8	Doe, John	06:00	08:00	10:20	12:40
9	Doe, John	14:30	17:00	19:20	22:05
Sheet4

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

Hope that helps.

Excel 2010
	H	I	J
2	FALSE	TRUE	FALSE
3	FALSE	FALSE	FALSE
4	FALSE	FALSE	TRUE
5	FALSE	FALSE	TRUE
6	TRUE	FALSE	TRUE
7	FALSE	TRUE	TRUE
8	FALSE	FALSE	FALSE
9	FALSE	FALSE	FALSE
Sheet4

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

 

[lynzlou]

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



[TABLE="width: 666"]
<tbody>[TR]
[TD="class: xl65, width: 196"]SAHAR, SAKHI AHMAD[/TD]
[TD="class: xl66, width: 118, align: right"]11:00[/TD]
[TD="class: xl66, width: 120, align: right"]13:00[/TD]
[TD="class: xl66, width: 119, align: right"]17:05[/TD]
[TD="class: xl66, width: 113, align: right"]18:30[/TD]
[/TR]
</tbody>[/TABLE]


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???

[TABLE="width: 568"]
<tbody>[TR]
[TD="width: 363"]ALCE, KEVIN-ELLIOTT[/TD]
[TD="class: xl64, width: 56, align: right"]4:00[/TD]
[TD="class: xl64, width: 45, align: right"]6:00[/TD]
[TD="class: xl64, width: 52, align: right"]9:25[/TD]
[TD="class: xl64, width: 52, align: right"]11:30[/TD]
[/TR]
</tbody>[/TABLE]

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

but it is working for other entries
[TABLE="width: 568"]
<tbody>[TR]
[TD="width: 363"]ANSAR, SYED[/TD]
[TD="class: xl66, width: 56, align: right"]4:00[/TD]
[TD="class: xl66, width: 45, align: right"]6:10[/TD]
[TD="class: xl66, width: 52, align: right"]8:05[/TD]
[TD="class: xl66, width: 52, align: right"]11:30[/TD]
[/TR]
</tbody>[/TABLE]


I am seriously confused is there a way to attach my spreadsheet so it makes more sense to look at?

 

[Peter_SSs]

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
	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
Incorrect break times

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
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

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.