Forum Discussion
EXCELL Conditional formatging
this is my report and were monitoring who left the job site early and not just back and forth between job sites... so we want the last time out instead of all the times in between and I pull this report from our badging system..
Thanks.
Select I3 and down, as far as needed.
I3 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=AND(I3<>"". I3<TIME(17,20,0))
Click Format...
Activate the Fill tab.
Select light red as fill color.
Click OK, then click OK again.Repeat, but with the formula
=AND(I3<>"", I3<TIME(16,0,0))
and light green as fill color.
Finally, repeat the steps with formula
=AND(I3<>"", I3<TIME(14,0,0)
and amber as fill color.
- and that will only highlight the last time out for each employee...
My apologies, I was too hasty. Here are the correct formulas:
=AND(I3<>"", I3=MAXIFS($I$3:$I$1000, $C$3:$C$1000, C3, $E$3:$E$1000, E3), I3<TIME(17,20,0))
=AND(I3<>"", I3=MAXIFS($I$3:$I$1000, $C$3:$C$1000, C3, $E$3:$E$1000, E3), I3<TIME(16,0,0))
=AND(I3<>"", I3=MAXIFS($I$3:$I$1000, $C$3:$C$1000, C3, $E$3:$E$1000, E3), I3<TIME(14,0,0))
