Conditional Formatting due, overdue, upcoming dates

webba

New Member
Joined
Dec 2, 2016
Messages
2
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

shanesuzanne

New Member
Joined
Dec 1, 2016
Messages
21
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

New Member
Joined
Dec 2, 2016
Messages
2
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

New Member
Joined
Dec 1, 2016
Messages
21
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top