Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting

I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Conditional Formatting

Assuming the dates are in column A, for the 10 days or less condition try
Formula Is
=(AND(A1-TODAY()<=10,A1-TODAY()0))

Select A1, then select column A, then select Format Conditional
Formatting and set up your conditions & formatting.

Hope this helps,

Hutch

"SharonR" wrote:

I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting

Hi Tom

Unfortunately that didn't work. Any other suggestions?

Shar

"Tom Hutchins" wrote:

Assuming the dates are in column A, for the 10 days or less condition try
Formula Is
=(AND(A1-TODAY()<=10,A1-TODAY()0))

Select A1, then select column A, then select Format Conditional
Formatting and set up your conditions & formatting.

Hope this helps,

Hutch

"SharonR" wrote:

I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Conditional Formatting

Hi Tom

Unfortunately this didn't work. Do you have any other suggestions?

FYI...I have to have 2 sets of criteria for the same column. One to turn
the date orange 10 days before expiry, and Two to have the date turn red once
the expiry is met.

Shar

"Tom Hutchins" wrote:

Assuming the dates are in column A, for the 10 days or less condition try
Formula Is
=(AND(A1-TODAY()<=10,A1-TODAY()0))

Select A1, then select column A, then select Format Conditional
Formatting and set up your conditions & formatting.

Hope this helps,

Hutch

"SharonR" wrote:

I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Conditional Formatting

I didn't include the 2nd condition because your orignal post said you already
had that. Here is what I used for the 2nd condition (to turn expired dates
red):

Formula Is =(AND(A1-TODAY()<=0,LEN(A1)0))

I'm sorry the formula I supplied didn't work for you. It (and the 2nd
condition above) work in my test workbook whether the dates are entered as
real Excel dates or as text. If you are sure you are applying the conditional
formatting correctly, then can you tell me anything else about your workbook
to help figure out why the formatting is not working correctly? Or, maybe an
MVP will have an insight for us.

Hutch

"SharonR" wrote:

Hi Tom

Unfortunately this didn't work. Do you have any other suggestions?

FYI...I have to have 2 sets of criteria for the same column. One to turn
the date orange 10 days before expiry, and Two to have the date turn red once
the expiry is met.

Shar

"Tom Hutchins" wrote:

Assuming the dates are in column A, for the 10 days or less condition try
Formula Is
=(AND(A1-TODAY()<=10,A1-TODAY()0))

Select A1, then select column A, then select Format Conditional
Formatting and set up your conditions & formatting.

Hope this helps,

Hutch

"SharonR" wrote:

I'm trying to get dates that are getting ready to expire in 10 days to turn
orange, then once the date has expired the date turns red. I've got the 2nd
criteria for the red text but can't get the formula to turn the date orange
10 days before expiry.....PLEASE HELP!!

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
How can I convert conditional formatting into explicit formatting? Patrick Harris Excel Discussion (Misc queries) 0 April 9th 09 12:00 AM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 06:32 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 03:02 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 09:24 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"