highlight

[puiuluipui]

I have this formula
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))
How can i make this formula highlight the cell when the date it's one month
before today()
I want the cell to become red with one month before today

If A1 is 15.09.2008 and the "today" is 15.08.2008, the cell it's showing me
30 days and the cell to become red.

Thank you in advance

 

[Max]

Apply this as the CF's Formula Is:
=TODAY()>=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik

 

[David Biddulph]

Use conditional formatting. Excel help will tell you how.

 

[puiuluipui]

Hi, i have a small problem with conditional formatting. I tried conditional
formatting befor this question, but it doesn't highlight what i need. What i
need is "less than 30 days". But it's highlighting 138,116...etc. But it
doesn't highlighting everything. I dont know why. I only need what is less
than 30. Maybe the formula it's changing something.
What can i do?

Thanks.
"David Biddulph" a scris:

 

[puiuluipui]

The formula in each cell it's :
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))

in conditional formatting :

cell value is less than 30 days


..649 zile 293 zile 291 zile 310 zile 307 zile
..791 zile 827 zile 66 zile 87 zile expired
..302 days 311 days 311 days 313 days 445 days
..356 days 362 days 362 days 24 days 682 days
..44 days 25 days 25 days 30 days 17 days

highlighted cell (with red):

.. 293 zile 291 zile
..
..
.. 24 days
.. 25 days 25 days 17 days

Maybe the formula and conditional formatting view the cells that formula
it's working with.

Thanks.

"David Biddulph" a scris:

What can you do?
Well you can start by telling us what values you have in your cells, and
what conditional formatting conditions. Are you using Cell Value Is or
Formula Is? Copy the formula from the condition in Conditional Formatting
and paste it to this group so that we can see what you're using. [In doing
so, you may find that you've got something different from what you intended.
It may have unintended quotes around it, or it may have unintended absolute
addressing.]
--
David Biddulph

Hi, i have a small problem with conditional formatting. I tried
conditional
formatting befor this question, but it doesn't highlight what i need. What
i
need is "less than 30 days". But it's highlighting 138,116...etc. But it
doesn't highlighting everything. I dont know why. I only need what is less
than 30. Maybe the formula it's changing something.
What can i do?

Thanks.
"David Biddulph" a scris:

 

[David Biddulph]

Your problem is that you are trying to compare your cell contents which are
a text string. If you wanted to do that, your "cell value is" "less than"
value would need to look like ="30 days".

The problem, however is that a string comparison is alphanumeric.
"14 days" and "105 days" are less than "30 days", but "5 days" is not less
than "30 days". If you want to compare numbers, don't make them text.
Leave out the &" days" from your formula, and in your CF use "less than"
=30. If you want the " days" to show up in the cell, use a custom format
such as General" days".
--
David Biddulph

The formula in each cell it's :
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))

in conditional formatting :

cell value is less than 30 days


.649 zile 293 zile 291 zile 310 zile 307 zile
.791 zile 827 zile 66 zile 87 zile expired
.302 days 311 days 311 days 313 days 445 days
.356 days 362 days 362 days 24 days 682 days
.44 days 25 days 25 days 30 days 17 days

highlighted cell (with red):

. 293 zile 291 zile
.
.
. 24 days
. 25 days 25 days 17 days

Maybe the formula and conditional formatting view the cells that formula
it's working with.

Thanks.

"David Biddulph" a scris:

What can you do?
Well you can start by telling us what values you have in your cells, and
what conditional formatting conditions. Are you using Cell Value Is or
Formula Is? Copy the formula from the condition in Conditional
Formatting
and paste it to this group so that we can see what you're using. [In
doing
so, you may find that you've got something different from what you
intended.
It may have unintended quotes around it, or it may have unintended
absolute
addressing.]
--
David Biddulph

Hi, i have a small problem with conditional formatting. I tried
conditional
formatting befor this question, but it doesn't highlight what i need.
What
i
need is "less than 30 days". But it's highlighting 138,116...etc. But
it
doesn't highlighting everything. I dont know why. I only need what is
less
than 30. Maybe the formula it's changing something.
What can i do?

Thanks.
"David Biddulph" a scris:

Use conditional formatting. Excel help will tell you how.
--
David Biddulph

I have this formula
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))
How can i make this formula highlight the cell when the date it's
one
month
before today()
I want the cell to become red with one month before today

If A1 is 15.09.2008 and the "today" is 15.08.2008, the cell it's
showing
me
30 days and the cell to become red.

Thank you in advance

 

[puiuluipui]

IT'S WORKING!
Thank you.

"David Biddulph" a scris:

Your problem is that you are trying to compare your cell contents which are
a text string. If you wanted to do that, your "cell value is" "less than"
value would need to look like ="30 days".

The problem, however is that a string comparison is alphanumeric.
"14 days" and "105 days" are less than "30 days", but "5 days" is not less
than "30 days". If you want to compare numbers, don't make them text.
Leave out the &" days" from your formula, and in your CF use "less than"
=30. If you want the " days" to show up in the cell, use a custom format
such as General" days".
--
David Biddulph

The formula in each cell it's :
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))

in conditional formatting :

cell value is less than 30 days


.649 zile 293 zile 291 zile 310 zile 307 zile
.791 zile 827 zile 66 zile 87 zile expired
.302 days 311 days 311 days 313 days 445 days
.356 days 362 days 362 days 24 days 682 days
.44 days 25 days 25 days 30 days 17 days

highlighted cell (with red):

. 293 zile 291 zile
.
.
. 24 days
. 25 days 25 days 17 days

Maybe the formula and conditional formatting view the cells that formula
it's working with.

Thanks.

"David Biddulph" a scris:

What can you do?
Well you can start by telling us what values you have in your cells, and
what conditional formatting conditions. Are you using Cell Value Is or
Formula Is? Copy the formula from the condition in Conditional
Formatting
and paste it to this group so that we can see what you're using. [In
doing
so, you may find that you've got something different from what you
intended.
It may have unintended quotes around it, or it may have unintended
absolute
addressing.]
--
David Biddulph

Hi, i have a small problem with conditional formatting. I tried
conditional
formatting befor this question, but it doesn't highlight what i need.
What
i
need is "less than 30 days". But it's highlighting 138,116...etc. But
it
doesn't highlighting everything. I dont know why. I only need what is
less
than 30. Maybe the formula it's changing something.
What can i do?

Thanks.
"David Biddulph" a scris:

Use conditional formatting. Excel help will tell you how.
--
David Biddulph

I have this formula
=IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days"))
How can i make this formula highlight the cell when the date it's
one
month
before today()
I want the cell to become red with one month before today

If A1 is 15.09.2008 and the "today" is 15.08.2008, the cell it's
showing
me
30 days and the cell to become red.

Thank you in advance