Conditional Formatting Question

[Kevinzzz]

Hi all, I'm trying to find the right custom formula to achieve the following goal:

Presets:
Employee Name: Column B
Break 1 Start Time: Column F
Break 1 Back Time: Column G
Lunch Start Time: Column H
Lunch Back Time: Column I
Break 2 Start Time: Column J
Break 2 Back Time: Column K

Goal: Break Start time and Back time will be manually entered by me, the goal is to highlight(or change color) the employee name while they are on break (the time betweem start and back). And return the cell format to normal when back time ends. There are also posibilities that an employee only gets 1 break and no lunch or second break, so if lunch start time or break 2 start time = XXX (not needed), the formula should only highlight employee name during the first break.

I have tried multiple formulas such as MOD, TIME, NOW and nothing seems to click, any help would be appreciated!!!

 

[NARAYANK991]

Hi ,

Please upload your workbook after entering as much data as possible.

Narayan

 

[Kevinzzz]

Hi ,

Please upload your workbook after entering as much data as possible.

Narayan

Here is the sample excel file, just FYI the actual file is on Google Docs this is the downloaded version.

 

[p45cal]

=AND(MOD(NOW(),1)>=$F3,MOD(NOW(),1)<=$G3)
Use in Conditional formatting after selecting cells starting in C3 downwards.
See attached where conditional formatting has been applied (I have changed values in F3 and G3 for testing). Not needed, but the formula is also in cell Q3.
This should get you started.

Have a go with the likes of:

=OR(AND(MOD(NOW(),1)>=$F3,MOD(NOW(),1)<=$G3),AND(MOD(NOW(),1)>=$H3,MOD(NOW(),1)<=$I3),AND(MOD(NOW(),1)>=$J3,MOD(NOW(),1)<=$K3))

to include more breaks/lunches.