Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Roy
 
Posts: n/a
Default Conditional Formating

I have set up in conditonal formating to change cell patterns to be a certain
color based on the date. The below conditional formating formula works, now
what I need to for the words (Red, Yellow, or Green to automatically appear
in the cell based on the color i.e if cell color is Red I what the word Red
to appear in the cell. How can I do this?

=F2<=TODAY() (pattern color Red)
=F2<=TODAY()+5 (pattern color Yellow)
=F2<=TODAY()+25 (pattern color Green)

  #2   Report Post  
greg7468
 
Posts: n/a
Default


Hi Roy,

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW",IF (F2<=TODAY()+25,"GREEN")))

HTH.

Greg.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=373867

  #3   Report Post  
Roy
 
Posts: n/a
Default

Greg,

Thanks that worked.

Now I have another issue. The spread sheet I have created is basically an
action plan (column A Date Opened, column B Issue, column C Root Cause,
column D Action Plan, column E Responsible, column F Date Due, column G Date
Complete, column H Status R/Y/G) is my issue, the date due column could be
out as far as 30 + days (i.e. date open is 5/25/05, date due could be
6/25/05) when I put in the 6/25 date column H says it is false, when infact
it should be Green. What can I do to correct either the cell formula or the
formula in CF or both?

Thanks for your help,
Roy

"greg7468" wrote:


Hi Roy,

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW",IF (F2<=TODAY()+25,"GREEN")))

HTH.

Greg.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=373867


  #4   Report Post  
greg7468
 
Posts: n/a
Default


Roy,
It returns a value of false because it is outside the +25 days from
today rule you have set.

I assume you are only really interested when the cell changes to yellow
then red, whilst it's green it doesn't matter how many days away the due
date is.

If this is the case why not just change the formula to

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW","G REEN"))

This will be green for anything over 5 days away from today.

If this does not work for you, what do you want it to return if the due
date is 30 days away from today.

HTH

Greg.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=373867

  #5   Report Post  
Roy
 
Posts: n/a
Default

Greg,

Thanks that worked.


"greg7468" wrote:


Roy,
It returns a value of false because it is outside the +25 days from
today rule you have set.

I assume you are only really interested when the cell changes to yellow
then red, whilst it's green it doesn't matter how many days away the due
date is.

If this is the case why not just change the formula to

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW","G REEN"))

This will be green for anything over 5 days away from today.

If this does not work for you, what do you want it to return if the due
date is 30 days away from today.

HTH

Greg.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=373867


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formating Daniell Excel Discussion (Misc queries) 3 May 10th 05 06:48 PM
Expanding conditional formating with reference cells changing CCoop Excel Discussion (Misc queries) 2 May 4th 05 03:36 PM
more than 3 conditional formating in excel Manan Excel Discussion (Misc queries) 2 February 7th 05 10:12 PM
Conditional Formating when result is text Lary Excel Worksheet Functions 1 December 16th 04 03:13 AM
30 Day Aging Report Using Conditional Formating Tiegris Excel Worksheet Functions 0 November 4th 04 11:07 PM


All times are GMT +1. The time now is 02:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"