Excel 2013 - Conditional Formatting per row

[linkazoid]

Hi,

 

 

I'm trying to make it easier for the staff who are monitoring staff attendance to check if people are late.

 

I have an Excel sheet with Staff names and their start time. I want to apply conditional formatting to highlight if they are on time or late. Any conditional formatting that I apply always seems to reference back to C2. I've tried to apply the formatting and copy and fill the formatting values but this didn't work either. Is anyone able to work some excel magic on the attached worksheet.

 

Thanks

 

Michael

Timesheet.xlsx

[LosOjos]

Hi,

 

 

I'm trying to make it easier for the staff who are monitoring staff attendance to check if people are late.

 

I have an Excel sheet with Staff names and their start time. I want to apply conditional formatting to highlight if they are on time or late. Any conditional formatting that I apply always seems to reference back to C2. I've tried to apply the formatting and copy and fill the formatting values but this didn't work either. Is anyone able to work some excel magic on the attached worksheet.

 

Thanks

 

Michael

 

You need to remove the absolute references and make them relative; just remove the $ before the row number.

 

e.g. if you're formatting data in column D based on a value in column C, such as if d > C, starting at row 2 your conditional format would be ">$C2" This tells Excel always in column C (the $ before the column), but relative to row 2

 

Example attached might make more sense

Timesheet.xlsx

[linkazoid]

I can't see any obvious difference in you sheet, compared to my original sheet (Different to the uploaded one) but yours seems to work!

Is it possible to add it to turn green if early or on time?

 

Thanks,

 

Michael

[LosOjos]

I can't see any obvious difference in you sheet, compared to my original sheet (Different to the uploaded one) but yours seems to work!

Is it possible to add it to turn green if early or on time?

 

Thanks,

 

Michael

 

Difference is like I said - you need to remove the lock on row (the $ symbol). If you check your sheet, the formula was ">$C$2", whereas it needed to be ">$C2" (notice the second $ is gone)

 

To make a condition that looks if they arrive on time, it'd be "=$C2", or early "<$C2" or even on time or earlier "<=$C2"