# Conditional Formatting for Forecasting Dates

#### Creospan

##### New Member
Hello! I am JT.

I have Windows 8 using Microsoft Excel 2010

I am trying to use Conditional Formatting to forecast dates in Column A based upon the blanks in Column B to do (3) things:

1. Trying to get the dates in Column A to turn red when the dates in Column B is blank and is over 31 days over the date in Column A. For example, 11/10/2014 would be red because there is a blank in Column B and is glaringly over 31 days from 11/10/2014.
2. Trying to get the dates in Column A to turn yellow when Column B is blank and is within 30 days of the date in Column A.
3. Trying to get the dates in Column A to turn blue when Column B is blank and is within 14 days of the date in Column A.

I would appreciate specifics on what the formula would look to make this happen, if possible.

Thank you.
 Column A Column B ETA Actual 4/13/2015 4/2/2015 11/10/2014 5/4/2015 5/18/2015 4/4/2015 3/23/2015

<tbody>
</tbody>

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
How can a blank be within 30 days of a date? I guess you mean non-blank?

If so then the formulas for the rules in cell A2 are in this order (red, blue, yellow):
Code:
``````=ABS(A2-B2)>31
=ABS(A2-B2)<=14
=ABS(A2-B2)<=30``````

This assumes difference between A and B, no matter which one is earlier.

MarcelBeug:

Thank you for your timely response.

Hey! I only ask the questions in the way my team provides it. But to elaborate. The team only wants to track the blank spaces in Column B and to determine how long they have been blank in reference to Column A's date. Hope that makes better sense. My first time out. I'll give your input a try. Thanks for being there.

JT

Replies
1
Views
189
Replies
1
Views
550
Replies
2
Views
321
Replies
5
Views
332
Replies
21
Views
441

1,207,090
Messages
6,076,520
Members
446,211
Latest member
b306750

### 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?

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