Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Big Abalone
 
Posts: n/a
Default I want any date 90 days or older from current date change color

I am trying to set up a worksheet for work. I would like to have some
conditional formatting. In the date worked column will be dates going back
months if not years. I would like to set up color changes for dates that are
90, 120, 150 days older then the date at the time of opening the worksheet
=now(). Dates that are less then 90 days will have no color.

I am a fisherman and with this worksheet I could tell in a glance how long
it had been since I last worked an area.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JohnGuts
 
Posts: n/a
Default I want any date 90 days or older from current date change color


If A1 has the cell you want to change color and it contains the date the
last time you were in an area, try the following:

FORMAT/CONDITIONAL FORMATING

In Condition 1 choose FORMULA IS from the dropdown
In the formula area type =NOW()-A190

Then hit condition and format as you wish (change background, text
color, etc)

Do the same and change 90 to what ever length you want to check for.

This will subtract A1 from today's date, and if 90, it will format as
you set.

Happy & Safe fishing.

John


--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=535275

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Big Abalone
 
Posts: n/a
Default I want any date 90 days or older from current date change col



"JohnGuts" wrote:


If A1 has the cell you want to change color and it contains the date the
last time you were in an area, try the following:

FORMAT/CONDITIONAL FORMATING

In Condition 1 choose FORMULA IS from the dropdown
In the formula area type =NOW()-A190

Then hit condition and format as you wish (change background, text
color, etc)

Do the same and change 90 to what ever length you want to check for.

This will subtract A1 from today's date, and if 90, it will format as
you set.

Happy & Safe fishing.

John


--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=535275

Thanks I will give it a go tomarrow...beat now, will let you know

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JohnGuts
 
Posts: n/a
Default I want any date 90 days or older from current date change color


OK good luck. FYI, you can use up to 3 conditions in the conditional
formating. Do the 1st one with 90 days, the second with 120, etc.

John


--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=535275

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Big Abalone
 
Posts: n/a
Default I want any date 90 days or older from current date change col



"JohnGuts" wrote:


OK good luck. FYI, you can use up to 3 conditions in the conditional
formating. Do the 1st one with 90 days, the second with 120, etc.

John

--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
View this thread: http://www.excelforum.com/showthread...hreadid=535275

Ok, it works except the cells in the date column without dates also are filled with color. Is there a way to not to have the empty cells filled. Every other cell in the date column is empty. Can I keep them from coloring also.







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default I want any date 90 days or older from current date change col

See your post in the excel group.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Big Abalone" wrote in message
...


"JohnGuts" wrote:


OK good luck. FYI, you can use up to 3 conditions in the conditional
formating. Do the 1st one with 90 days, the second with 120, etc.

John

--
JohnGuts
------------------------------------------------------------------------
JohnGuts's Profile:

http://www.excelforum.com/member.php...o&userid=30174
View this thread:

http://www.excelforum.com/showthread...hreadid=535275

Ok, it works except the cells in the date column without dates also are

filled with color. Is there a way to not to have the empty cells filled.
Every other cell in the date column is empty. Can I keep them from coloring
also.





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
Macro To Change Cell Color When Value Changes carl Excel Worksheet Functions 4 March 14th 06 09:24 PM
How do I add a range by date over 90 days older than today John DeLosa Excel Discussion (Misc queries) 4 February 16th 06 10:30 PM
Extracting data from the current date Cali00 Excel Worksheet Functions 2 April 14th 05 05:49 AM
extract date from the most current date Cali00 Excel Discussion (Misc queries) 1 April 13th 05 02:05 PM
Identifying the Active Fill Color Steve Conary Excel Discussion (Misc queries) 3 December 9th 04 05:45 AM


All times are GMT +1. The time now is 03:07 PM.

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

About Us

"It's about Microsoft Excel"