Conditional Formatting Help with Date Formula

[lindy76]

I have a few issues that I need help with. I am charged with training individuals every 365 days in a specific topic, but their dates are all different, so I need to have the due date change colors based on the time remaining before they expire. Yellow for 75 days before and red for 45 days before. I also have a few other issues as well, listed below. If someone could help me I would greatly appreciate it. I have a copy of the worksheet I am trying to use but upload it here for someone to look at.
Column G is where I have the date automatically populate to a year after the date of their last training. Example, if I enter the date in F as 12/25/09, in G in auto populates as 12/25/10. But it does that for all the cells in G even if I haven't entered the date in F yet. I need it to look for a date in F before it adds a date in G.

I thought this would be a simple project when I was told to do it, now I know why no one has done it yet.

• I have formatted the first two dates (G2 & G3) to auto populate the date in G from the data in F ..... =EDATE(F2,12)

• How to I get rid of the dates in G that are not associated with a date in F?

• I would like the date to change to yellow when it is 75 days prior to the date in G and then Red 45 days prior to the date in G

Thanks for your time in advance.

 

[WhatTheXL]

How to I get rid of the dates in G that are not associated with a date in F?

Try:

=IF(NOT(ISBLANK(F2)),"Populate Date","")

 

[markmzz]

Lindy76,

Welcome to the board.

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

Use the link below for helper.

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

Markmzz

 

[lindy76]

Do I put this before or after the code that I have there now? Here is the code for column G. Sorry I have very little excel knowledge. Thanks for your help!

=EDATE(F2,12)

Try:

=IF(NOT(ISBLANK(F2)),"Populate Date","")

 

[WhatTheXL]

I would like the date to change to yellow when it is 75 days prior to the date in G and then Red 45 days prior to the date in G

Condition 1

=$G$2<=TODAY()+45

Make Red



Condition 2

=$G$2<=TODAY()+75

Make Yellow

 

[WhatTheXL]

=if(not(isblank(f2)),edate(f2,12),"")

 

[lindy76]

It works great for G2 but does not work for the rest of the column, any suggestions on what I am doing wrong? You are saving my backside! I have been searching the internet for over six hours.

Condition 1

=$G$2<=TODAY()+45

Make Red



Condition 2

=$G$2<=TODAY()+75

Make Yellow

 

[lindy76]

This code worked perfectly. Only thing now is getting the whole column to work with the color changing issue. Thanks for the help so far!

=if(not(isblank(f2)),edate(f2,12),"")

 

[lindy76]

I just noticed that the code is not reading the data correctly. The date listed in the cell is 8/1/2011, it turned the cell yellow instead of red. I then set it to 12/25/2011 and it stayed yellow.

Condition 1

=$G$2<=TODAY()+45

Make Red



Condition 2

=$G$2<=TODAY()+75

Make Yellow

 

[WhatTheXL]

When you open conditional formatting

is the

=$G$2<=TODAY()+45

Red listed first as condition 1

P.S.
you'll need to remove the $'s to copy the formatting to other cells