Conditional Formatting due, overdue, upcoming dates

[webba]

I know about setting conditional formatting for "this month", "next month" and "last month" but it does not really do what I want because if the date is more than 30 days overdue the formatting will not apply to the cell. Here's my question. I have a date entered in say Cell A1 which automatically tells Cell B1 to set the date 90 days ahead. In Cell B1 I want to use conditional formatting so the cell will be yellow if the date is this month, red if the date has passed, and blue if the date is next month. I'm not sure it's possible though because if I complete the project today (by due date) I will enter today's date in Cell A1 which starts the calculation over again. Make sense? I'm trying to find a way to alert myself that I have something due this month, it's overdue, or it's coming up. Is there a formula that will allow me to do this?

Example:

Cell A1 data Cell B1 data
6/6/16 (automatically calculates 6 months ahead) 12/6/16

This month it would be Yellow because it's due. If I did not complete it this month or next it needs to be red. If I complete it I will enter the date completed in Call A1 which will recalculate B1. I'm sure it's much easier than I'm explaining it but I'd appreciate some guidance.

Anthony

 

[shanesuzanne]

Can you clarify?

When you say "month" in regards to Yellow, Red, Blue - are you talking within 30 days of todays date? Or specifically the month of December?

Per your example:

6/6/16 + 6 months = 12/6/16

11/30/2016 = yellow? 1/2/17 = yellow?

Or only yellow from 12/1 to 12/31?


And when you mentioned that once it's complete you enter the complete date in A1 to recalculate B1. So, you have to do the process again in 6 months, like a loop? Or once it's done, you don't have to do it again?

 

[webba]

Can you clarify?

When you say "month" in regards to Yellow, Red, Blue - are you talking within 30 days of todays date? Or specifically the month of December?

Per your example:

6/6/16 + 6 months = 12/6/16

11/30/2016 = yellow? 1/2/17 = yellow?

Or only yellow from 12/1 to 12/31?


And when you mentioned that once it's complete you enter the complete date in A1 to recalculate B1. So, you have to do the process again in 6 months, like a loop? Or once it's done, you don't have to do it again?

Me: I would want the cell highlighted yellow for the month of December if it's due at some point in December and yes the process is done every six months like a loop. So essentially I could have 6/6/16 in Cell A1 which prompts the date 12/6/16 in cell B1 and when the task is completed in December I would add that date so it could be Cell A1 would say 12/2/16 if I completed it today which would prompt B1 to calculate the date 6/2/17.

I only want to highlight the cells if they are due or if the date is past due

Thank you
Anthony

 

[shanesuzanne]

You need to setup two conditional format rules on the cell - one for if it was before this month (red), one if it is this month (yellow). We won't color the cell if the month hasn't occurred yet - so no rule is needed.

Don't do the built in formats - instead chose the option that says "Use a formula to determine which cells to format". Assuming the first date is in A1, and the due date is in B1 - here are the formulas you need:

Set the formatting for this one to RED highlighting - it highlights if the month is before the current month:
=DATE(YEAR(B1),MONTH(B1),DAY(1))<DATE(YEAR(NOW()),MONTH(NOW()),DAY(1))

Set the formatting for this one to YELLOW highlighting - it highlights if the month is equal to the current month (same formula - just = instead of <):
=DATE(YEAR(B1),MONTH(B1),DAY(1))<DATE(YEAR(NOW()),MONTH(NOW()),DAY(1))

Then, using the paintbrush, copy the conditional formats down all the cells in column B. Usually the biggest pain when working with conditional formats IMO is copying them to the others in the range - that part takes patience and trial/error.

Good luck!