Solved formula for changing cell colour and working

September 8, 2010 at 04:35:47
Specs: Windows 7
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

See More: formula for changing cell colour and working

Report •


✔ Best Answer
September 8, 2010 at 07:59:55
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.



#1
September 8, 2010 at 06:11:12
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 green

What does "coming up to the three year period 6- months" mean?


Report •

#2
September 8, 2010 at 06:50:53
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.

Report •

#3
September 8, 2010 at 07:24:46
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

http://www.skeptic.com/


Report •

Related Solutions

#4
September 8, 2010 at 07:59:55
✔ 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.


Report •

#5
September 8, 2010 at 08:08:41
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

Report •

#6
September 8, 2010 at 10:21:00
thank you very much, perfect, it works, that is fantastic!!!!

Report •

#7
September 8, 2010 at 12:58:32
Glad I could help.

Report •

#8
September 9, 2010 at 05:48:18
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

Report •

#9
September 9, 2010 at 06:33:20
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!


Report •

#10
September 9, 2010 at 10:57:59
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?

Report •

#11
September 9, 2010 at 14:02:00
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


Report •

#12
September 9, 2010 at 22:59:58
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?

Report •

#13
September 10, 2010 at 10:50:44
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

http://www.skeptic.com/


Report •

#14
September 10, 2010 at 11:02:07
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


Report •

#15
September 10, 2010 at 11:20:06
Humar, thanks for the update.

MIKE

http://www.skeptic.com/


Report •

#16
September 10, 2010 at 11:33:51
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.

Report •

#17
September 10, 2010 at 14:08:53
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


Report •

#18
September 10, 2010 at 23:59:43
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


Report •

#19
September 11, 2010 at 07:00:13
The sequence of 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 line

Like;

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

http://www.skeptic.com/


Report •

#20
September 11, 2010 at 10:56:06
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


Report •

#21
September 11, 2010 at 11:56:12
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

http://www.skeptic.com/


Report •

#22
September 11, 2010 at 20:20:19
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

http://www.skeptic.com/


Report •

#23
September 13, 2010 at 05:52:24
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?

Report •

#24
September 13, 2010 at 08:58:02
Hi,

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

Regards


Report •

#25
September 13, 2010 at 09:40:59
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 GREEN

3 years = 36 months
2 years 6 months = 30 months

So now you tell me, how much is:

2 years =
1 year 6 months =

MIKE

http://www.skeptic.com/


Report •

#26
September 14, 2010 at 00:05:12
So now you tell me, how much is:

2 years = 24
1 year 6 months = 18

Right?

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


Report •

#27
September 14, 2010 at 04:29:35

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

http://www.skeptic.com/


Report •

#28
September 14, 2010 at 06:34:55
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 forum in which someone said:

"you cannot use any add-in for conditional formatting and EDATE is part of the ATP add-in"



Report •

#29
September 14, 2010 at 08:15:51
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

http://www.skeptic.com/


Report •


Ask Question