Condional Formating

[Guest]

I have a spreadsheet which has expiration dates in Column B I am using this
formula in coditional formating to have it highlight the row when its 30
days from expiration.

=IF(AND(ISNUMBER($E18),$E18<(TODAY()+30)),TRUE,FALSE)

I need it be be from 0-30 days and I need one for 31-60 days...can anyone
tell me how to do this...or tell me if there is a better way? Also, how can
I protect the formula but allow folks to do the data entry?

thanks, I am trudging the road to happier spradsheets!
-- Jules

 

[Guest]

Your one for 30 days works fine.

Here is one for 60 days
=IF(AND(ISNUMBER($E18),$E18<(TODAY()+60),$E18>(TODAY()+30)),TRUE,FALSE)

As far as protection goes, just (1) lock the cell with Format > cell >
protection, and (2) protect the worksheet, and in the options under tools >
protect worksheet, don't allow them to change formatting.

 

[Guest]

You can simplify this if it is for conditional formatting. Just use this
portion of it in a "formula is" format.

=AND(ISNUMBER($E18),$E18<(TODAY()+30))

Set that condition as your first, then add a second condition of:

=AND(ISNUMBER($E18),$E18<(TODAY()+60))


The first condition will apply if the date is within 30 days, and the 2d
will apply when within 60 days. Even though the days overlap, the first
condition takes precedence.

 

[Guest]

1. spreadsheet which has expiration dates in Column B I
2. from 0-30 days

Format>Conditional>Formatting>Condition 1
Formula is : =AND($B18>=TODAY(),($B18-TODAY())<=30,($B18-TODAY())>=0)
Format choose color 1

3. for 31-60 days

Format>Conditional>Formatting>Condition 2
Formula is : =AND($B18>=TODAY(),($B18-TODAY())<=60,($B18-TODAY())>=31)
Format choose color 2

.....hope you have fun choosing the color...

4. To Protect : learn to read Help files...it will do good to be happy with
your spreadsheets.

 

[Guest]

driller, thanks for the formula...I'm havinig problems though....

=AND($E$1:$E$101)>=TODAY(),($E$1:$E$101-TODAY())<=60,($E$1:$E$101-TODAY())>=31)

I need to have it cover the row so I modified your formula and it's not
working...can you pleae help me again? The expiration date is in colum E so
E1:E101 will work.

Thanks so much for your time.

 

[Guest]

driller, thanks for the formula...I'm havinig problems though....

=AND($E$1:$E$101)>=TODAY(),($E$1:$E$101-TODAY())<=60,($E$1:$E$101-TODAY())>=31)

I need to have it cover the row so I modified your formula and it's not
working...can you pleae help me again? The expiration date is in colum E so
E1:E101 will work.

 

[Guest]

1. spreadsheet which has expiration dates now in Column E not B
2. from 0-30 days
click Row 18 > if u see it completely dimmed > go to
Format>Conditional>Formatting>Condition 1
Formula is : =AND($e18>=TODAY(),($e18-TODAY())<=30,($e18-TODAY())>=0)
Format choose color 1

then
3. for 31-60 days

Format>Conditional>Formatting>Condition 2
Formula is : =AND($e18>=TODAY(),($e18-TODAY())<=60,($e18-TODAY())>=31)
Format choose color 2
hit enter
then copy the whole row 18
then select all rows downward or upward > see it dimmed?
then click Edit>Paste Special>click format only>enter

4. To Protect : learn to read Help files...it will do good to be happy with
your spreadsheets.

 

[Guest]

Hi diller...will the formula work throughout the column? I think that is
when I got into trouble....I tried to change it to a range...to no avail

Thanks Diller.

 

[Guest]

try it first in a save as New workbook, not in your original file...Copy
paste by Row not by Column...Row is horizontal....

 

[Guest]

Okay...I did this and it's not working....I also tried Allllen's
method...don't know what I'm doing wrong...I pasted the formating to the
rows....using both scenarios...I've changed the dates to see if it worked
(ensure the formatting was there...) to no avail....

Sign me frustrated in B'more

 

[Guest]

?
1. Your expiration dates on column E must be later than today()
15November2006 - in your computer.
can u paste here the expiration dates?

 

[Guest]

Driller...here are the exp dates...in Column E and this workbook will grow...
EXP DATE
10/01/10
08/09/09
06/12/09
06/12/09
06/12/09
06/12/09
08/18/09
08/18/09
08/18/09
08/18/09
06/29/09
06/29/09
06/16/09
06/16/09
06/16/09
05/18/11
05/18/11
05/18/11
05/17/11
05/17/11
05/17/11
07/14/09
07/14/09
02/27/09
02/27/09
01/22/09
01/22/09
01/22/09
01/22/09
01/22/09
01/22/09
12/20/08
12/20/08
12/20/08
12/20/08
09/12/09
09/12/09
09/12/09
11/03/08
11/03/08
11/03/08
04/01/08
11/03/08
04/20/09
04/20/09
09/12/09
05/10/09
05/10/09
05/10/09
09/27/08
09/27/08
01/13/06
07/29/07
07/29/07
01/01/08
08/26/08
07/18/08
07/18/08
07/29/07
07/31/09
11/01/06
07/29/08
07/29/08
07/29/08
07/19/08
09/08/08
08/02/08
08/02/08
08/02/08
08/02/08
07/19/08
10/09/08
08/26/08
09/08/08
04/26/07
08/02/08
09/08/08
01/31/08
10/10/08
10/09/08
10/10/08
06/09/07
12/10/07
06/09/07
06/15/07

01/06/08
09/27/07
04/26/07
08/02/08
05/06/07
09/08/08
10/10/07
10/13/07
02/09/07
12/12/07
12/12/07
04/26/07
05/28/08
05/06/08
06/05/08
03/15/08
11/03/08
10/12/07
06/15/08
10/10/08
10/13/07
03/07/08
03/07/08
10/16/07
10/16/07
06/03/08
06/03/08
09/15/06
10/12/07
10/10/07
05/06/08
06/25/08
11/02/07
11/02/07
10/20/07
10/20/07
01/11/08
12/01/07
03/30/08
03/30/08
06/27/08
11/28/07
11/28/07
11/28/07
07/28/10
08/11/07
07/18/08
08/01/08
05/08/08
05/08/08
02/12/09
02/07/09
02/07/09
04/11/08
02/07/09
02/08/09
03/30/09
03/30/09
04/18/08
07/18/08
08/03/08
08/03/08
03/27/07
06/01/09
11/01/08
12/31/07
03/01/09
03/01/09
07/29/08
04/29/07
11/12/06
07/30/08
07/30/08
06/01/09
04/30/09
06/01/09
11/30/08
11/30/08
11/30/08
11/30/08
08/31/08
08/31/08
12/31/07
08/31/08
08/31/08
07/31/08
07/31/08
11/30/07
11/30/07
05/01/08
02/28/08
05/01/07
06/15/07
12/14/07
06/29/07
12/05/07
12/21/07
09/13/06
09/28/06
09/12/07
04/05/09
02/26/09
03/20/09
04/30/13
07/31/07
09/06/07
07/01/08
07/31/08

 

[Guest]

Jules...your dates are not within the 60 day range start from today 15
nov.06...the nearmost date is 09 Feb. 2007....
Try changing one date like Nov.30 or Dec. 30. 2006...

 

[Guest]

u still there ?

Jules...your dates are not within the 60 day range start from today 15
nov.06...the nearmost date is 09 Feb. 2007....
Try changing one date like Nov.30 or Dec. 30. 2006...

 

[Guest]

Yep...at home now...I did change the dates to accomedate the condition...it
didn't work. I need this spread sheet this week.....yikes! my original
formula worked on the past dated (expired)items...but not when I changed the
dates to see if it worked...I changed some to january some to later this
month...and nothing happened.

Thanks for your help driller.