Conditional Formatting (based on dates in other cells)

[Generalist]

Hi,

Hopefully someone can help me out with this challenge:

I want to change the color in 2 columns based on how far before/after a set date in another column.

In Column F there will be stated a date that a job has to be completed within (expiration date).
In Column G I want the cells to change to red color if the date in Column F is within the range of +/- 30 days (30 days before expiration, and 30 days overdue).
In Column H I want the cells to change to yellow color if the date in Column F is within the range of +/- 7 days (7 days before expiration, and 7 days overdue).

Is this possible to achieve by using conditional formatting?

Thank you.

[Jonmo1]

Try something like this for Red.
=ABS(TODAY()-F2)<=30
Change 30 to 7 for Yellow.

However, if you're using this on alot of cells, I'd suggest putting =TODAY() into it's own designated cell, say Z1 for example.
Then use
=ABS($Z$1-F2)<=30

[Generalist]

Wow- quick reply!

I am afraid I need some more guidance on how to choose the correct conditional formatting option.
Are you sure that TODAY is correct to use if the dates in Column F (expiration dates) is constantly varying?

[Jonmo1]

Well how do you determine if a date is expired?
By comparing a given date (column F) to Today's current date (TODAY())
So yes, TODAY() is the correct function.

So first put today's date in an available designated cell.
Say Z1 for example (you can use any cell you want, doesn't even have to be on the same sheet).
Z1: =TODAY()

Now highlight the range you want to change colors based on the dates in f being within expiration date.
Say G2:G100 (don't do the entire column)
Now click Conditional Formatting - New Rule - "Use a Formula to determine..."
Put in the formula
=ABS(TODAY()-F2)<=30
Make the F2 reflect the TOP cell of the range you selected.

Apply yourdesired format and click OK

Repeat for column G, but change 30 to 7

Hope that helps.

[Generalist]

Perfect! Thank you very much for your help!
Excel is fun when you get it right

One more question:
Is it possible to have the number of days to/after expiration date in the cells of column G and H, based on the expiration date in F?

Example: Date in F: 20.10.2015
Info in G: RED and "+1" (1 day to expiration)
Info in H: RED and "+1" (1 day to expiration)

Date in F: 01.10.2015
Info in G: RED and "-19" (19 days since expiration)
Info in H: RED

[Jonmo1]

OK so you want the TEXT +1 to appear in the G cell if the date in F is expired..

This is NOT conditional formatting.
You can put this standard formula in the cell of column G, say G2

=TODAY()-F2

This will return the number of days between today and the expiration date.
If it's expired (F2 is in the past), the number will be positive.
If it's not expired yet (F2 is in the future), the number will be negative.

If you want the opposite of that, it would be
=F2-TODAY()

[Generalist]

Yes, like that! But is there a way to make the text (numbers) in G and H be based on the different dates in their "corresponding" cells in column F?

Column F, Row 5 : 20.10.2015 = -1 in Column G, Row 5
Column F, Row 6: 27.10.2015 = +6 in Column G, Row 6
Column F, Row 7: 25.10.2015 = +4 in Column G, Row 7
Etc

I have a large sheet with many different dates.

Thank you once again for extremely quick and good replies!

[Jonmo1]

That's what that formula does, it references the date in column F
=F2-TODAY()

I must be missing something

[Generalist]

It works. I had not formatted the column to "numbers".
Empty cells are now filled fit "-42299,00" and the color red (guess the red is something I have done my self). How do I make empty cells stay blank (no numbers in them) ?

[Jonmo1]

To handle the blanks.

In conditional formatting, the formula would be adjsted to
=AND(F2<>"",ABS(TODAY()-F2)<=30)

The regular formula in the cell
=If(F2="","",F2-TODAY())

[Generalist]

I can not get it to work.
When i enter the regular formula (=If(F2="","",F2-TODAY()) in G2 I only get an error. "",""F2 is highlighted as an error.

Any ideas?

[Jonmo1]

Try changing the commas to semicolons ( , to ; )

[Generalist]

Thank you, Sir!
Works like a charm

Not sure if I should start a new thread or not, but how do I make a date and time picker control in each cell in Column F (expiration date Column), and have it affect the rest of the sheet as it does now?

[Jonmo1]

You're welcome.

Yes, you should probably start a new thread for that.

[Generalist]

Still trying to optimize this spreadsheet..

In Column G (days to expiration) there is now numbers in every cell showing how many days to expiration, or after expiration. I have used conditional formatting to mark the cells that expires in +/- 7 days with red.

I would like to have only the cells in Column G that are marked red to show me how many days until/after expiration (+/- 7 days), and nothing in the cells in Column G that have an expiration date that is before/after +/- 7 days.

Example: Column F (expiration date): 2.11.2015
Column G (red if +/- 7 days): red and "2"

Example: Column F (expiration date): 4.12.2015
Column G (red if +/- 7 days): white/blank (no color and no number)

Hope someone could have a look at this!

[Jonmo1]

Try

=IF(AND(F2<>"",ABS(TODAY()-F2)<=7),TODAY()-F2,"")

[Generalist]

Thank you very much, Jonmo1!

Can I bother you with one more newbie question?
I now got a green triangle / exclamation mark in the upper left corner of each cell in G (after I entered the new formula). It says something like "the cell differs from the column formula".
Any way to get rid of this on all cells?