If/Then Formula for Monthly Task Tracking

[Jennifer242]

Hi all, hoping someone can help me with a formula that will change the colour of a cell to blue when tasks are due based on a month. IE with a March year end, there are tasks due in Feb, Aug, Dec, and July and we need the task to fill blue when that month comes up indicating that it's due. I hope that makes sense...

 

[Micron]

You are asking for a conditional formatting formula? Then you'd want it to be based on the year also so that the cell is blue only if the date value applies to the current month/year date? Then what might work for you is a rule expression (formula) like
=AND(MONTH($A11) = MONTH(NOW()),YEAR($A11) = YEAR(NOW()))

You would have to edit to suit the cells that you want it to apply to. You could test the formula on a sheet by putting dates in A11 if you are not sure about editing the cell reference in that formula.

 

[Jennifer242]

It is a yearly calendar thing so it wouldn't be dependent on the year ongoing. What we need is if the year end is October, then the PIC #1 should go blue in March and PIC #2 in September, etc. We'd start it again fresh each year. I'm really just not strong enough with formulas to figure this out

 

[Micron]

You might get by with a cf rule like =MONTH($A11) = MONTH(NOW)) and set the applies to property to the range you need.

I tested with that set to range A11:A19. Seems to do what you want as long as the year does not matter. Note that without considering it, 2 Jan cells are blue but they are different years. So you open an old workbook that has this and the cf rule will be applied whether you want it to or not.