Timesheet conditional formatting: changing the colour of a cell base on time worked

Hi,

I have some experience with basic conditional formatting but overall I don't know much and would appreciated any help I could get.

I have timesheets that I need employees to fill out their start and end times. What I want to happen is if their shift is longer or shorter than 8.5 hours I want the cells with their shift time to change colour to Light red but with darker red text.
I have no trouble creating a total time row under each person which I can use conditional formatting to change colour based on duration of the shift by using =Cell<8.5 and =Cell>8.5 but I'm hoping to find a way to do this without using a total time row.

I have attached a sample timesheet, what I want to happen is that if the shift duration is more or less than 8.5 hours, cells B3:C4 will change to Light red with darker red font, is this possible?

Regards,

Alex

Hi Alexlambo,
Welcome to Excelforum.

Select B3:C4 and then use the following formula for conditional formatting:

Formula:

Please Login or Register  to view this content.

Also see attached.
HTH!

Originally Posted by jewelsharma

Hi Alexlambo,
Welcome to Excelforum.

Select B3:C4 and then use the following formula for conditional formatting:

Formula:

Please Login or Register  to view this content.

Also see attached.
HTH!

Hi Jewelsharma,

Thank you so much for your help!! I have searched everywhere and could not find this answer so I definitely appreciate it.

The only problem I am having now is that when I remove and the start and end time the cell stays red, is it possible to add a condition that if there is no start or end time that the cell stays the normal colour?

Regards,

Alex

Try ...

=AND(COUNT($C$3:$C$4)=2,ABS($C$4-$C$3)<>8.5/24)

seems you've already been helped by one of the very best among us. Please mark the thread as [SOLVED]; and do consider adding reputation to Phuocam for his solution.

You guys are both amazing!! you have both made my life so much easier, thanks so much for the help

You're welcome!

Hi,

Sorry to reopen this thread but I have stumbled across my next problem. This conditional formatting mentioned above works really well, the only problem I have now is copying this across 60 people for 7 days a week. Normally clicking and dragging is great but I can't get it to work because of the $ next to each number and letter which I can't remove.

Do you have any suggestions for the best way to do this which doesn't involve manually entering the conditional formatting into each cell?

Regards,

Alex