Conditional Formatting using IF and AND

Question

I have created a spreadsheet that tracks vacation accruals based on the number of years worked.  I want to have conditional formatting setup to highlight cells that exceed the maximum accrual allowance, but am having difficulty creating the appropriate formula.  Here's an example:

Employee	Hire Date	Balance
John Smith	1/1/10	100
Jane Smith	1/1/00	200

I want to highlight the balance column if the amount is greater than the maximum accrual allowed.  Maximum accruals are based on tenure with the company as follows:

1-4 years    160 hours

5-15 years  240 hours

16+ years   320 hours

I feel there needs to be an IF statement as well as an AND statement, with additional statements embedded within for each max accrual.

Thanks in advance for your help!

Answer 1

Hi,

Select C2 then drag to select column C values then

Home tab | conditional formatting | new rule | use a formula to decide which cells to format and enter this formula.

=C2>IF(DATEDIF(B2,TODAY(),"Y")>=16,320,IF(DATEDIF(B2,TODAY(),"Y")>=5,240,160))

Click the format button | fill tab | choose a colour and OK out.

Answer 2

Thanks, Mike.  This worked perfectly.  Is there a way to make it so the cell with that conditional formatting does not highlight when there is no text in the "Employee" cell?

Hi,

Try this one

=AND(A2<>"",C2>IF(DATEDIF(B2,TODAY(),"Y")>=16,320,IF(DATEDIF(B2,TODAY(),"Y")>=5,240,160)))

Answer 3

Thanks, Mike.  This worked perfectly.  Is there a way to make it so the cell with that conditional formatting does not highlight when there is no text in the "Employee" cell?

Answer 4

Hi,

Try this

1. In range F2:F4, type 1.5.16
2. In range G2:G4, type 4,15,100
3. In range H2:H4, type 160,240,320
4. Click on cell C2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
5. In the Formula bar there, enter this formula

=$C2>VLOOKUP(DATEDIF($B2,TODAY(),"y"),$F$1:$H$4,3,1)

6. Click on Format > Fill > Red > OK > Apply
7. Now copy the Conditional formatting down.

Hope this helps.