|
highlight
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 |
highlight
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 --- "puiuluipui" wrote: 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 |
highlight
Use conditional formatting. Excel help will tell you how.
-- David Biddulph "puiuluipui" wrote in message ... 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 |
highlight
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 "puiuluipui" wrote in message ... 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 |
highlight
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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 |
highlight
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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 |
highlight
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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 |
highlight
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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 "puiuluipui" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com