Conditional Formatting Dates Greater Than 6 Months... 
New Excel Help Service!
We need your input to help create a new Live Online Excel Help service.
Please take our survey and let us know your thoughts!
Have a great day!
Don


Conditional Formatting Dates Greater Than 6 Months...  Excel 
View Answers 
I have a spreadsheet where I need dates greater than 6 months to show up in red. This is what I currently have in the appropriate cell...
cell value is: greater than or equal to =today()180
This is not working, can anyone help? Thanks
cell value is: greater than or equal to =today()180
This is not working, can anyone help? Thanks
Similar Excel Tutorials
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Create a 12 Month Calendar With The Current Day Highlighted in Excel
 Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
 Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel
Output the Name of the Current Excel Workbook Including Extension  UDF
 Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
 Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
Highlight the Row of the Selected Cell
 This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
 This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
 This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
 This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Similar Topics
Hi everyone,
I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple.
There are 3 conditions I would like to apply to cell B2:
If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red.
If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange.
If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow.
I am sure I am missing something quite simple here. Can anyone point me in the right direction?
Thanks,
ACurtis802
I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple.
There are 3 conditions I would like to apply to cell B2:
If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red.
If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange.
If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow.
I am sure I am missing something quite simple here. Can anyone point me in the right direction?
Thanks,
ACurtis802
What is the proper way to construct a formula for an if then statement in order to solve for a value (i.e. "x") that is in cell A1 meet the following criteria:
If X is greater than or equalt to 3.50 . . . then the answer is 1
If X is greater than or equal to 3.00 and less than 3.50 . . . then the answer is 2
If X is greater than or equal to 2.50 and less than 3.00 . . . then the answer is 3
If X is greater than or equal to 0.00 and less than 2.50 . . . then the answer is 4
Thanks!
If X is greater than or equalt to 3.50 . . . then the answer is 1
If X is greater than or equal to 3.00 and less than 3.50 . . . then the answer is 2
If X is greater than or equal to 2.50 and less than 3.00 . . . then the answer is 3
If X is greater than or equal to 0.00 and less than 2.50 . . . then the answer is 4
Thanks!
I'm trying to do something that I think is simple but just can't get the last conditional format.
I have 2 columns B (weight) & C (% up or down) with 32 Rows. It is a weight loss chart that colour codes the ups and downs.
Cells in Column C have the formula "=IF(B32="","",((B32B31)/B31))" to leave the cell blank if the cell in column B has no typed in value.
The formatting I have is as follows:
1. If Cell C32 is Greater than 0 shade it Red  Works
(Cell Value Is less than 0)
2. If Cell C32 is Less than 0 shade it Green  Works
(Cell Value Is Greater than 0)
3. If Cell B32 is Empty than C32 should have no colour.  Problem is that Cells Stay Red.
Formula Is =IF(ISBLANK(B32),"",IF(B32=0,B32,""))
Any quick ideas?
....but now that I read it a couple times I would need something that also states if cell in column C is equal to zero do not shade as well.
I have 2 columns B (weight) & C (% up or down) with 32 Rows. It is a weight loss chart that colour codes the ups and downs.
Cells in Column C have the formula "=IF(B32="","",((B32B31)/B31))" to leave the cell blank if the cell in column B has no typed in value.
The formatting I have is as follows:
1. If Cell C32 is Greater than 0 shade it Red  Works
(Cell Value Is less than 0)
2. If Cell C32 is Less than 0 shade it Green  Works
(Cell Value Is Greater than 0)
3. If Cell B32 is Empty than C32 should have no colour.  Problem is that Cells Stay Red.
Formula Is =IF(ISBLANK(B32),"",IF(B32=0,B32,""))
Any quick ideas?
....but now that I read it a couple times I would need something that also states if cell in column C is equal to zero do not shade as well.
I've been trying to get this to work for two days to no avail. Here's what I'm trying to do:
Conditionally format a cell so that if blank, one color, if >= a specified cell, green, and if less than the same specified cell, red. I also don't want the conditional formatting rule to fire if the cell contains text.
I can't figure out how to tell the formatting rule not to fire if there is text in the "target cell" (the cell to which the conditional formatting is applied) while formatting based on comparison to a reference cell. If I didn't need it to ignore text, I could do it.
In this example, G12 is the cell to which I'm applying the conditional formatting. G10 is the reference cell (the cell to which G12 is being compared)
[cell value] [is equal to] [=""] (this one works)
[cell value] [is greater than or equal to] [=AND(NOT(ISTEXT(G12)),$G$10)] this should turn green but doesn't work at all in this configuration
[cell value] [is less than] [=AND(NOT(ISTEXT(G12)),$G$10)] should turn yellow if G12 doesn't contain text and is less than G10. instead, anything I enter turns the cell yellow.
I've also tried this:
[cell value] [is equal to] [=""]
[cell value] [is greater than or equal to] [="$G$10,AND(NOT(ISTEXT(G12)))"]
[cell value] [is less than] [="$G$10,AND(NOT(ISTEXT(G12)))"]
this works for a blank cell, but turns the cell green if G12 contains text and yellow for anything else that's entered.
and this:
[cell value] [is equal to] [=""]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12>=$G$10)]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12
Conditionally format a cell so that if blank, one color, if >= a specified cell, green, and if less than the same specified cell, red. I also don't want the conditional formatting rule to fire if the cell contains text.
I can't figure out how to tell the formatting rule not to fire if there is text in the "target cell" (the cell to which the conditional formatting is applied) while formatting based on comparison to a reference cell. If I didn't need it to ignore text, I could do it.
In this example, G12 is the cell to which I'm applying the conditional formatting. G10 is the reference cell (the cell to which G12 is being compared)
[cell value] [is equal to] [=""] (this one works)
[cell value] [is greater than or equal to] [=AND(NOT(ISTEXT(G12)),$G$10)] this should turn green but doesn't work at all in this configuration
[cell value] [is less than] [=AND(NOT(ISTEXT(G12)),$G$10)] should turn yellow if G12 doesn't contain text and is less than G10. instead, anything I enter turns the cell yellow.
I've also tried this:
[cell value] [is equal to] [=""]
[cell value] [is greater than or equal to] [="$G$10,AND(NOT(ISTEXT(G12)))"]
[cell value] [is less than] [="$G$10,AND(NOT(ISTEXT(G12)))"]
this works for a blank cell, but turns the cell green if G12 contains text and yellow for anything else that's entered.
and this:
[cell value] [is equal to] [=""]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12>=$G$10)]
[cell value] [is equal to] [=AND(NOT(ISTEXT(G12)),G12
I would like to have all numbers in column D to be highlighted if greater than column F. I chose the entire column and selected "Greater Than..." within Conditional Formatting, but I only get the error: "You cannot use a direct reference to a worksheet range in a conditional formatting formula." Any special formula for doing this?
Is it possible to use standard conditional formatting over 3 overlapping values?
I have a range of cells a5:a57. I want try and do the following
If is greater than A1 then format red text
If is greater than preceeding cell (ie A5>A6, A6>A7 etc) then format orange fill
If greater than A1 and greater than preceeding cell then format red text and orange fill.
The values as examples could be
A1=100
A5 = 104
A6 = 105
or A= 99
A5 = 97
A6 = 98
I can use =IF(AND) for the 3rd criteria, and cell value for the first two.
However when used it only applies the first criteria. I assume this is because the ranges being tested overlap.
Can anyone help?
I have a range of cells a5:a57. I want try and do the following
If is greater than A1 then format red text
If is greater than preceeding cell (ie A5>A6, A6>A7 etc) then format orange fill
If greater than A1 and greater than preceeding cell then format red text and orange fill.
The values as examples could be
A1=100
A5 = 104
A6 = 105
or A= 99
A5 = 97
A6 = 98
I can use =IF(AND) for the 3rd criteria, and cell value for the first two.
However when used it only applies the first criteria. I assume this is because the ranges being tested overlap.
Can anyone help?
Hi
Can you someone help me solve this problem. I have a list of numbers in one column and the column to the right contains a smiley I generate using wingdings.
I need formula that will
1) If value is less than 10 it will show a sad face with red background.
2) If value is greater than 10 it will show straight smile and orange background
3) If value greater than 20 it will be happy face with green background.
I can get the green and red but the orange is confusing me. I am using straightforward if function but I want to be able to use excels way of display greater than but less than.
Thanks forum!
Can you someone help me solve this problem. I have a list of numbers in one column and the column to the right contains a smiley I generate using wingdings.
I need formula that will
1) If value is less than 10 it will show a sad face with red background.
2) If value is greater than 10 it will show straight smile and orange background
3) If value greater than 20 it will be happy face with green background.
I can get the green and red but the orange is confusing me. I am using straightforward if function but I want to be able to use excels way of display greater than but less than.
Thanks forum!
Hi,
Have a problem trying to get a formula to work.
I am trying to set a scorecard up where it will give points depending on the % actual.
For example:
if less than 60%, then 0,
if equal to or greater than 60% but less than 75%, then 1
if equal to greater than 75% but less than 80%, then 2 and so on.
I have been playing around with the example below, but can't seem to get it working.
[php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]
Maybe it 's because I pickled my brains last night.
Cheers,
Daywalker
Have a problem trying to get a formula to work.
I am trying to set a scorecard up where it will give points depending on the % actual.
For example:
if less than 60%, then 0,
if equal to or greater than 60% but less than 75%, then 1
if equal to greater than 75% but less than 80%, then 2 and so on.
I have been playing around with the example below, but can't seem to get it working.
[php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]
Maybe it 's because I pickled my brains last night.
Cheers,
Daywalker
I would like to highlight the smallest value in cells E3:Q3 that is greater than zero what formula do I need?
I have been playing with If statements to attempt to work out if a value in a cell is more than 7 days difference from Today().
I would like to do this as one formula rather than haveing a formula to work out the difference with a value in a different cell and then calling on the cell to see if it is greater/less than (x).
I've tried all the variations I can think of but can't quite get the syntax of greater than "X" of today.
If A1 is greater than "x" days of Today() then True, False.
Does anyone have a solution please?
Hello  I am trying to write a formula to return the next date after today, but am having some trouble with it. Here is my scenario:
In cell K25 I have a value where if it is greater than 49 I want the forumla to look at the values of Cells L3:L22 (these cells have a range of dates in them). I want the formula to go through these cells and pick the next date after today....If the value in Cell K25 is Less than or equal to 49 I want it to just return Todays date.
I found this post http://www.excelforum.com/excelgene...tertoday.html which was basically what I was looking for, but for some reason when I put that into my formula it will return the smallest date, even if it is older than today.
So going back to my formula....Here is what I have currently in my cell:
=IF(K25>49,MIN(IF(L3:L22>TODAY(),L3:L22)),TODAY())
Like I mentioned above  this results with dates that are older than today  what can I do to make it return results that are only after todays date?
Thanks for any help you can provide!
In cell K25 I have a value where if it is greater than 49 I want the forumla to look at the values of Cells L3:L22 (these cells have a range of dates in them). I want the formula to go through these cells and pick the next date after today....If the value in Cell K25 is Less than or equal to 49 I want it to just return Todays date.
I found this post http://www.excelforum.com/excelgene...tertoday.html which was basically what I was looking for, but for some reason when I put that into my formula it will return the smallest date, even if it is older than today.
So going back to my formula....Here is what I have currently in my cell:
=IF(K25>49,MIN(IF(L3:L22>TODAY(),L3:L22)),TODAY())
Like I mentioned above  this results with dates that are older than today  what can I do to make it return results that are only after todays date?
Thanks for any help you can provide!
Hi all
I am trying to use if formula to satisfy 5 conditions, for example:
in column D i have all different percentages, but if the percentage is equal or greater than 4, then I want to return the value 5 to column E,
if it's equal or greater than 2 but smaller than 4, then column E should show up as 4,
if it's between 1 and 2, but smaller than 2, then column E should show up as 3,
if it's greater than 1 but smaller than 4, then column E should show up as 2,
if it's equal or greater than 4, then column E should show up as 1,
Any clue on how to satisfy these 5 conditions in 1 formula?
Much appreciated!
Harry
I am trying to use if formula to satisfy 5 conditions, for example:
in column D i have all different percentages, but if the percentage is equal or greater than 4, then I want to return the value 5 to column E,
if it's equal or greater than 2 but smaller than 4, then column E should show up as 4,
if it's between 1 and 2, but smaller than 2, then column E should show up as 3,
if it's greater than 1 but smaller than 4, then column E should show up as 2,
if it's equal or greater than 4, then column E should show up as 1,
Any clue on how to satisfy these 5 conditions in 1 formula?
Much appreciated!
Harry
Im trying to use the IF function but cant seem to find what the symbol for greater than or equal too is for it. I can get it to work using greater than, but i need it to work with equal too aswell.
Also is there way to make the cell change colour aswell as what it says in a IF function, for example the box turns green when the IF function puts Yes in the cell, and red when it puts no?
Edit: Is there anyway to make certain cells hidden when the sheet is protected?
Also is there way to make the cell change colour aswell as what it says in a IF function, for example the box turns green when the IF function puts Yes in the cell, and red when it puts no?
Edit: Is there anyway to make certain cells hidden when the sheet is protected?
Hello all,
Attached is a spreadsheet with a graded color scale applied to the values in column D that have a value greater than or equal to 40. I have also filled the rows when the value in column D is greater than or equal to 40 with various colors depending on the value. How would I go about applying the graded color scale to the entire row instead of using several rules?
Thanks
Attached is a spreadsheet with a graded color scale applied to the values in column D that have a value greater than or equal to 40. I have also filled the rows when the value in column D is greater than or equal to 40 with various colors depending on the value. How would I go about applying the graded color scale to the entire row instead of using several rules?
Thanks
Hi Folks
I have calculated the difference in working time between two dates. All good so far. Output cell is in format custom format hh:mm.
I now need to see whether the above is greater than four hours. I just tried a simple if clause, but this is not working.
Has anyone any ideas?
Thanks
I have calculated the difference in working time between two dates. All good so far. Output cell is in format custom format hh:mm.
I now need to see whether the above is greater than four hours. I just tried a simple if clause, but this is not working.
Has anyone any ideas?
Thanks
Help determining if a date or number of days is greater then or less then a date.
Computer System: Excel 2007, Windows XP
In Cell A2, list of dates i.e. 12/30/2010
I would like to create a formula (IF statement maybe?) to see if the date in A2 is less then 14 days, then return "3", if false then check to see if the date in A2 is greater than "15" days, but less than "28" days, then return "4", if false, then check to see if A2 is equal to "29", "30", "31", if true then return "5".
I have come up with "=IF(U3="15"
Computer System: Excel 2007, Windows XP
In Cell A2, list of dates i.e. 12/30/2010
I would like to create a formula (IF statement maybe?) to see if the date in A2 is less then 14 days, then return "3", if false then check to see if the date in A2 is greater than "15" days, but less than "28" days, then return "4", if false, then check to see if A2 is equal to "29", "30", "31", if true then return "5".
I have come up with "=IF(U3="15"
OK, I have a user that needs to check for multiple conditions based on the following:
Return value of "5" if value of cell 'n3' is < 80% of cell 'P3' OR cell 'N3" is less than 250
Return value of "4" if value of cell 'n3' is less than 90% or greater than or equal to 80% of cell 'P3' OR cell 'N3" is between 250 and 349
Return value of "3" if value of cell 'n3' is less than 100% or greater than or equal to 90% of cell 'P3' OR cell 'N3" is between 350 and 449
Return value of "2" if value of cell 'n3' is less than 110% or greater than or equal to 100% of cell 'P3'
Return value of "1" if value of cell 'n3' is less than 120% or greater than or equal to 110% of cell 'P3'
Return value of "0" if value of cell 'n3' is 120% or more of cell 'P3'
A coworker came up with the following nested IF statement, and while it does not error out, the formula returns an incorrect value...
=IF(OR(OR(N3<250,N3=P3),N3<(0.8*P3)),5, IF(OR(OR(N3>=250,N3<=349), OR(N3>=(0.8*P3),N3<(0.9*P3))),4,IF(OR(OR(N3>=350,N3<=449),OR(N3>=(0.9*P3),N3<P3)),3,IF(OR(N3>=P3,N3< (1.1*P3)),2,IF(OR(N3>=(1.1*P3),N3<(1.2*P3)),1,IF(OR(N3>=(1.2*P3)),0,"E"))))))
Return value of "5" if value of cell 'n3' is < 80% of cell 'P3' OR cell 'N3" is less than 250
Return value of "4" if value of cell 'n3' is less than 90% or greater than or equal to 80% of cell 'P3' OR cell 'N3" is between 250 and 349
Return value of "3" if value of cell 'n3' is less than 100% or greater than or equal to 90% of cell 'P3' OR cell 'N3" is between 350 and 449
Return value of "2" if value of cell 'n3' is less than 110% or greater than or equal to 100% of cell 'P3'
Return value of "1" if value of cell 'n3' is less than 120% or greater than or equal to 110% of cell 'P3'
Return value of "0" if value of cell 'n3' is 120% or more of cell 'P3'
A coworker came up with the following nested IF statement, and while it does not error out, the formula returns an incorrect value...
=IF(OR(OR(N3<250,N3=P3),N3<(0.8*P3)),5, IF(OR(OR(N3>=250,N3<=349), OR(N3>=(0.8*P3),N3<(0.9*P3))),4,IF(OR(OR(N3>=350,N3<=449),OR(N3>=(0.9*P3),N3<P3)),3,IF(OR(N3>=P3,N3< (1.1*P3)),2,IF(OR(N3>=(1.1*P3),N3<(1.2*P3)),1,IF(OR(N3>=(1.2*P3)),0,"E"))))))
I have tried to set up a list of cells to highlight in red any numerical values which are greater than 0 using conditional formatting.
This works fine, except that all blank cells are also highlighted in red.
Formula is currently:
cell value is greater than 0
What do I need to do to ignore the empty cells?
Thanks in advance
This works fine, except that all blank cells are also highlighted in red.
Formula is currently:
cell value is greater than 0
What do I need to do to ignore the empty cells?
Thanks in advance
I have an address book that has dates in columns G:K formatted
mm/dd/yyyy
I'm using this formula in conditional formatting to highlight this months birthdays (obviously without regard to year)
Code:
I need help modifying this to highlight next months birthdays
Thanks
mm/dd/yyyy
I'm using this formula in conditional formatting to highlight this months birthdays (obviously without regard to year)
Code:
=MONTH(G1)=MONTH(TODAY())
I need help modifying this to highlight next months birthdays
Thanks
Does anybody know a VBA code for a pop up meassage when a cell is greater than another? I have in cell AW59 a sum of AQ59:AV59 and if this number is greater than cell AN59 I'd like to have a pop up warning message saying you don't have enough inventory to do this.
Thanks
Windows XP
Excel 2003
Thanks
Windows XP
Excel 2003
I have a column of dates. I want to count the number of dates that are six
months or less from today. Any help would be appreciated.
kaye
months or less from today. Any help would be appreciated.
kaye
Hi guys,
So I want a range of cells (B2:B10) to have a data validation that any decimal entered has to be greater than or equal to A1.
A1 has a formula {=IF(MAX(A2:A10)<=0,"",MAX(A2:A10))} to find the max number in range A2:A10, if the max is <=0, show nothing.
This causes a problem for Data Validation in B2:B10 as it doesn't see A1 as equal to 0. Whenever I enter a number in B2:B10, it gives me an error (even when the number is greater than 0).
How do I make this data validation accept A1 as 0 while keeping it blank? Or is there another way to allow entry into B2:B10 without a number formulated in A1?
Please help!
So I want a range of cells (B2:B10) to have a data validation that any decimal entered has to be greater than or equal to A1.
A1 has a formula {=IF(MAX(A2:A10)<=0,"",MAX(A2:A10))} to find the max number in range A2:A10, if the max is <=0, show nothing.
This causes a problem for Data Validation in B2:B10 as it doesn't see A1 as equal to 0. Whenever I enter a number in B2:B10, it gives me an error (even when the number is greater than 0).
How do I make this data validation accept A1 as 0 while keeping it blank? Or is there another way to allow entry into B2:B10 without a number formulated in A1?
Please help!
Hi,
How can a range of cells that are dates be counted if the date in the cell
is prior to today.

Keith
How can a range of cells that are dates be counted if the date in the cell
is prior to today.

Keith
Hello Board,
I need a macro to display a comment based on the result of a formula. I tried to do data validation, but it doesn't work with formulas. So I opted for conditional formatting, now I need a macro to show a comment if a cell value is less than 4, then disappear once the cell's value is changed and is greater than 4. I want to explain the conditional formatting with a comment and let the user know that the other calculation aren't working because the value is less than 4. Can anyone help? Thanks in advance.
I need a macro to display a comment based on the result of a formula. I tried to do data validation, but it doesn't work with formulas. So I opted for conditional formatting, now I need a macro to show a comment if a cell value is less than 4, then disappear once the cell's value is changed and is greater than 4. I want to explain the conditional formatting with a comment and let the user know that the other calculation aren't working because the value is less than 4. Can anyone help? Thanks in advance.
Hi I want to insert conditional formatting to a range of cells so that if the number in the cell is greater than 30 it is coloured red. If it is lower than 3 then it is green. If it is between the range of 3 and 30 then it is amber. Has anyone any idea how this is done? I also want this to run a macro after I fully understand how to do it (I know this isnt the macro part but I just wanted to out it out there)
the ranges are ("H5:H19, H21:H24, H26:H29, H26:H29, H31:H36, H38:H44")