I have one column with a date which a task was done e.g. 12-12-2007, I have another column which needs to 'track' the progress, in this column i need the task coming up to the three year period 6- months to highlight green, then if the task is entering the 6+ months to highlight orange and if it is on the three year date (12-12-2010) it must be red signalling a reoccuring task must begin

✔ Best Answer

Let's say your date (12/12/2007) is in E4. Select F4

1 - From the Ribbon select Home - Styles - Conditional Formatting,

2 - From the drop down select 'Manage Rules' and select 'New Rule'

3 - In the dialog box select the last item in the list 'Use a formula to determine which cells to format'

4 - Enter the formulas below as a 3 separate Rules, clicking the format button then 'Fill' Tab and selecting a color for each Rule:=IF(TODAY()>$E$4+1095,TRUE,FALSE) Format with Red

=IF(TODAY()-$E$4>182,TRUE,FALSE) Format as Green

=IF(TODAY()-$E$4<182,TRUE,FALSE) Format as Orange

Each of these formula will use the TODAY() function (which always returns the current system date) to compare the current date to the date in E4.

1095 is three years worth of "days" (3 * 365) so if "today" is greater than E4 + 3 years, the cell will turn Red.

The other formulas use 182, which is approximately 6 months. Adjust as desired.

What you want to use is Conditional Formatting. Since we don't know what version of Excel you are running, we can't offer the implementation steps, but you could look it up in the Excel Help files or Google it for your version.

I'd offer the formula to use as the "condition" (<=Excel 2003) or "rule" (>=Excel 2007) but I'm cofused by this:

in this column i need the task coming up to the three year period 6- months to highlight greenWhat does "coming up to the three year period 6- months" mean?

I am working on Office 2010....I am looking for the formula to put in to calculate the following and then make them colours: say the job was audited in 12-12-2007, then in the next column is where i need to put the formula: highlight green if in 12-05-2010 (being six months and more prior to the next audit), highlight orange if in 12-06-2010 (being six months and less prior to next audit, which will allow us to flag that we need to start following up on audit), highlight red if 12-12-2010 as that means the job is now over auditing date.

date the job needs to be re-assessed is three years later

I am unsure of where to even start with the formulas.

And what happens once the job is audited? Does the cell with the 12/12/2007 get updated

or do you keep the date for historical purposes and start somewhere else?Is it always three years

or is there a second cell someplace that indicates when the next audit is due?MIKE

Let's say your date (12/12/2007) is in E4. Select F4

1 - From the Ribbon select Home - Styles - Conditional Formatting,

2 - From the drop down select 'Manage Rules' and select 'New Rule'

3 - In the dialog box select the last item in the list 'Use a formula to determine which cells to format'

4 - Enter the formulas below as a 3 separate Rules, clicking the format button then 'Fill' Tab and selecting a color for each Rule:=IF(TODAY()>$E$4+1095,TRUE,FALSE) Format with Red

=IF(TODAY()-$E$4>182,TRUE,FALSE) Format as Green

=IF(TODAY()-$E$4<182,TRUE,FALSE) Format as Orange

Each of these formula will use the TODAY() function (which always returns the current system date) to compare the current date to the date in E4.

1095 is three years worth of "days" (3 * 365) so if "today" is greater than E4 + 3 years, the cell will turn Red.

The other formulas use 182, which is approximately 6 months. Adjust as desired.

once the job is audited after three years i will then move the date to the colunmn 12-12-2007 and then the next column starts with the three years again. we don't really need the date for historical purposes but may be a good idea actually.

It is always three years, but the law will change next year to two years. but for now 3 years.

the problem is the audits are not happening on scheduled dates, some of the dates i have are 2005! so those will be red

thank you very much, perfect, it works, that is fantastic!!!!

Glad I could help.

I thought it worked last night but now it highlights Green, when it should be Orange. if we take 12-12-2007, three years from then would be 11-12-2010, my cell turns green, where it should be orange, as we are 3 months away from December? Then even when i change the date to play around my cell still stays green, please help

Sorry, I think I misunderstood what you were asking for a bit. I don't have time to test these too much so I'll leave it up to you.

Tip: One way to test these suggestions is to use versions of the the formulas directly in cells and see what the numbers come out as and/or see when they return TRUE or FALSE. That's easier than changing the Conditional Formatting over and over again. Once you get the structure you need, set up your CF's.

=IF(TODAY()>$E$4+1095,TRUE,FALSE) Format as Red

=IF(($E$4+1095)-TODAY()>182,TRUE,FALSE) Format as Green

=IF(($E$4+1095)-TODAY()<182,TRUE,FALSE) Format as Orange

Good luck!

Seems ok for now, will try a bit more later. I have a few more questions PLEASE. The audits which date back to 2002 and 2005,etc, how do i make the cells to red? do i add a formula +1095 and later?, how would i do that?

When I change the date in E4, F4 goes white, why is that?

can I copy the CF by copy and paste special - conditional formatting, is that right?

Hi,

re:can I copy the CF by copy and paste special - conditional formatting, is that right?I don't see paste special - conditional formatting even in Excel 2010.

PasteSpecial - Formatting will do it.How it applies conditional formatting will depend on how you used $ signs in the source cell.

If you had a CF =IF(A1=1,TRUE,FALSE) in cell E10,

and you copied and used PasteSpecial-Formatting to cell E11,

the CF would be: =IF(A2=1,TRUE,FALSE)If you had a CF =IF(A$1=1,TRUE,FALSE) in cell E10,

and you copied and used PasteSpecial-Formatting to cell E11,

the CF would be: =IF(A$1=1,TRUE,FALSE)The $ sign acts the same in CF as in standard formulas - the $ sign stops that part of the formula from changing - in the second of the above examples it stopped the row number changing when the CF formula was applied to different rows. The column didn't change because the CF was applied in the same column.

Regards

I have a few more questions PLEASE. The audits which date back to 2002 and 2005,etc, how do i make the cells to red? do i add a formula +1095 and later?, how would i do that?

When I change the date in E4, F4 goes white, why is that?

Try these formulas: =IF(TODAY()>=EDATE(E4,36),TRUE,FALSE) pattern RED

Must be first.

=IF(TODAY()>=EDATE(E4,30),TRUE,FALSE) pattern ORANGE (although Yellow might be better)

Must be second in line

=IF(TODAY()>=EDATE(E4,24),TRUE,FALSE) pattern GREEN

Last in line.

The =EDATE() function required that the ADD-IN Analysis Tool pack be installed,

it should be, but check just to make sure.MIKE

Hi, Re: EDATE() and EOMONTH()

These are both included in the standard functions in Excel 2010 and don't rely on the Analysis Toolpak anymore.

Regards

It's interesting that EDATE doesn't show the active link to the Help files as you enter it into the Formula bar. At least not on my machine running Excel 2003.

I can only guess that it is because it's not a standard function in 2003 and comes as part of the Analysis Toolpak. In Excel 2007 & 2010 the help does show up for both EDATE() and EOMONTH()

Regards

Humar

oh dear, still not working, my cell goes green when it should be orange. This one worked, but I just need to know what formula to add to make the red 3 years and more to turn read as some audits are many years overdue and need to go red.

=IF(TODAY()>$E$4+1095,TRUE,FALSE) Format as Red

=IF(($E$4+1095)-TODAY()>182,TRUE,FALSE) Format as Green

=IF(($E$4+1095)-TODAY()<182,TRUE,FALSE) Format as Orange

The sequenceof the formulas is important,

when looking at the Conditional Formatting window,

the three formulas must appear as

RED on top or first in line

ORANGE in the middle or second in line

GREEN on the bottom or third in lineLike;

TOP

=IF(TODAY()>=EDATE(E4,36),TRUE,FALSE) pattern RED

MIDDLE

=IF(TODAY()>=EDATE(E4,30),TRUE,FALSE) pattern ORANGE

BOTTOM

=IF(TODAY()>=EDATE(E4,24),TRUE,FALSE) pattern GREEN

If they are in any other sequence, then the logic of the whole thing fails.

MIKE

I didn't know that!! Very interesting.

It has worked, but I have a date of 12-12-2008, the next audit will take place on 12-12-2011, it stays white, what do I add to the formula to make it green, so anything before 6 months prior to the due date will also highlight green.Am I allowed to ask one more formula help question please.

I have a date which Items are due to be completed in one column, in the other column, same scenario, just different formulas.I need RED for 6 days before and over the due date continuously until resolved

I need ORANGE for one week before due date

I need GREEN if COMPLETED

Use DerbyDad03's version

Remember sequence is important:=IF(TODAY()>$E$4+1095,TRUE,FALSE) Format as Red

=IF(($E$4+1095)-TODAY()>182,TRUE,FALSE) Format as Green

=IF(($E$4+1095)-TODAY()<182,TRUE,FALSE) Format as Orange

MIKE

OK, I think I was finally able to wrap my head around this one.

Try this:RED

=IF(TODAY()>=EDATE(E4,36),TRUE,FALSE)

ORANGE

~~=IF(AND(TODAY()>EDATE(E4,30),TODAY()<EDATE(E4,36)),TRUE,FALSE)~~Shorter version:

=IF(TODAY()>EDATE(E4,30),TRUE,FALSE)GREEN

=IF(TODAY()<=EDATE(E4,30),TRUE,FALSE)

MIKE

it works, thank you , but what does the 36 and 30 stand for. If I need to do every two years, would I use 24 and then 30 for the 6 months?

Hi, Look at the Help file for EDATE() - it explains how it works and what the parameters are.

Regards

If I need to do every two years, would I use 24 and then 30 for the 6 months?Close, but no cigar....

=EDATE() works with months,

your original problem was 3 years plus turn RED,

2 years 6 months turn ORANGE

less than 2 years 6 months GREEN3 years = 36 months

2 years 6 months = 30 monthsSo now you tell me, how much is:

2 years =

1 year 6 months =MIKE

So now you tell me, how much is: 2 years = 24

1 year 6 months = 18Right?

then how would you work out days? If i had to do red=7 days, etc.

2 years = 24

1 year 6 months = 18

Right?You win the Cigar.....

then how would you work out days?For days, you would need formulas similar to DerbyDad03's originals in post #4

MIKE

FYI: I have just discovered that EDATE cannot be used for Conditional Formatting in Excel 2003. If you try to use it, you will get this message:

"You may not use references to other worksheets or workbooks for Conditioanl Formatting criteria."

I Googled around a bit and found this

forumin which someone said:"you cannot use any add-in for conditional formatting and EDATE is part of the ATP add-in"

Will have to make note of that. I'm pretty sure we can see similar questions in the future. Luckily this OP is using 2010, so it should be no problem.

Thanks DerbyDad03

MIKE

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History