Conditional Formatting based on what month we are in

[mskitty]

Hi

This is my first post so please be gentle!!

I can usually find what I am looking for through google but not this time so maybe it isn't possible...here goes:

I have created an annual leave planner in Excel 2013 for everyone in our office. On the summary sheet I have totalled the amount of annual leave everyone is entitled to, and the total leave taken so far. In an ideal world, to keep the office running efficiently the total annual leave taken should be:

=(totaldays/12)*how many months we are through the year. So for example, the total number of days allowance for the office is 280 and we are now in November so (280/12)*11=257 so around 257 days (with a 10% varience each way) should have been taken so far this year to ensure we don't have lots of people wanting to use their leave all at the same time at the end of the year or the opposite where everyone has used up their leave too early and haven't kept any to one side for emergencies etc.

I would like the total days taken cell to highlight either green or red depending on the above conditions. Is this possible? Does it make sense to anyone? How would l do it?

Many thanks

Michelle

 

[Jake Collins]

Dear Michelle

The following formula in conditional formatting will determine if cell A3 is within 10% of the pro rata number of the number of total days (which is stored in a named range TotalDays). So have the conditional formatting use this formula to set the cell green and then manually set the cell to be red.

=AND(A3>=TotalDays/12*MONTH(NOW())*0.9,A3<=TotalDays/12*MONTH(NOW())*1.1)

 

[mskitty]

Thank you - yes this works a treat.