If cell has date within certain timeframe, change color?

[shivers]

I have some tasks in column A, column B has dates they are due. If they dates are within a timeframe (say due by end of the current month, or within the next 30 days, etc.) then I would like the color of that date cell to change (red in this case).

For the columns, I have one column with:

Time until due
=DATEDIF(TODAY(),D4,"M")&" mo. "&DATEDIF(TODAY(),D4,"MD")&" days"

Beside that is Target date column, each cell has a date (simple 06/16/2016 for example)

So I'd like the "Time until due" cells to change color


Thanks a lot for any help with this!

[xenixman]

Here is some Conditional Formatting that should give you what your looking for.

Conditional Formatting 2007

1) Select your cell or range of cells, IE B1:B100
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter one of the formulas from below.
6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Select which formula you need:

90 Days before Today: =B1-TODAY()<90
60 Days before Today: =B1-TODAY()<60
30 Days before Today: =B1-TODAY()<30
15 Days before Today: =B1-TODAY()<15
5 Days before Today: =B1-TODAY()<5

See how that works for you.

[newdoverman]

If the Target Dates are in Column B starting in row 2 (row 1 for titles) select the dates in column B and enter this formula in Conditional Formatting, Use formula and format as RED. This will format all dates due within a calendar month of today.

Please Login or Register  to view this content.

The period of time makes a difference in the way the calculation can be made so please be more specific with your requirements.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[shivers]

Thanks for your help guys, I have tried both ways. For some reason xenixman's method doesn't work for a few cells in the range I set conditional formatting for, does for others. newdoverman's method works correctly. How can I change with that method the time frame (from 1 calendar month to rolling 30 days or next 2 weeks, etc.)?

Attached sample Excel file with the 2 columns, I want the column A (Time left) to be the one that gets red font highlighted based on time frame.

[newdoverman]

I added a helper column (can be hidden) that contained actual numeric data. I then added some Conditional Formatting rules to colour the background, that you should be able to follow. These rules are in 5 day increments and the order of the rules is important. The formulae are simple. All dates within 1 month have red font.
=a2<=5 =a2<=10 etc.

Any date more than 30 days doesn't have formatting applied to column B

[shivers]

I tried applying that but whereas the helper column you added has those numeric values, in my spreadsheet that column is text goal names ("Master acoustic arrangement" "Save $1500 for trip" etc.) along with a cost column, so I have attached a workbook now with the actual format of my sheet, a few excerpts.

[newdoverman]

I changed some of the dates so that the formatting will show. If I hadn't, all the values would have had the same formatting.

[shivers]

Thank you that is working great! I've learned how to tinker with the conditional rules too, much appreciated

[newdoverman]

Thank you for the feedback and the rep.