Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Conditional Formatting- Dates Greater Than 6 Months...

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

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 ...
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 ...
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 ...
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 ...

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
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
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
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
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

Similar Topics







Hello,

I have a cell range AA5:AA15 with 10 different numbers in each cell.

Is it possible to format a cell based on the following:

1. Greater than or equal to 365 but less than or equal to 730 = Orange

2. Greater than or equal to 731 but less than or equal to 1095 = Yellow

3. Greater than or equal to 1096 but less than or equal to 1460 = Grey

4. Greater than or equal to 1461 but less than or equal to 1825 = Blue

5. Greater than or equal to 1826 but less than or equal to 2190 = Green

6. Greater than or equal to 2191 = RED

Thank you,

Steve




Hi everyone

I hope you are well!

Basically I am looking for a formula for conditional formating so
1) the cell is green if the date is greater than 6 months away in the future from todays date
2) the cell is yellow if it is less than 6 months away in the future from todays date and
3) the cell is red if today's date is greater than the date in the cell.

I have attached a spreadsheet. Please feel free to give it a go

thanks

dates.xlsx

Mike

I have dates of items that were accomplished and would like to color code them to quickly see how long ago these items were accomplished...

Green for current to 9 months old
Yellow for 9 months old to 1 year old
Red for older then one year

The cell I wish to format contains dates so for example
Today is 7 Apr 2011
5 Apr 2011 would show up Green
20 Apr 2010 would show up yellow
1 apr 2010 would show up red.

The only problem I am having is I want them color coded from TODAYs date!

I have tried variations of -

Cell value is less then TODAY-270
Cell value is between Today-270 and Today-365
and
Cell value is greater then Today-365

Any help is appreciated...

thanks for any help you can provide.




Here's my problem:

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red

It seemed simple enough when I started but I'm having trouble figuring this one out. Any advice would be much appreciated.

Thank you,
Amanda

I know how to determine the months between two dates using the DATEDIF function but how can you do the same thing using VBA?

I have a column of dates in column A (named "future_dates") and am comparing them to today's date (cell named "date_today").

If the date_today is greater then a date in column A, the corresponding cell in column B (named "eval_window") will say "Overdue".

If date_today is not greater then a date in column A I need to count the number of months between today's date and the dates in the column.

If the number of months is greater then 5, I want to popluate the corresponding cell in column B with ">6".

If the number of months equals 5, I want to populate the corresponding cell in column B with "1".

If the number of months equals 4, I want to populate the corresponding cell in column B with "2".

If the number of months equals 3, I want to populate the corresponding cell in column B with "3".

If the number of months equals 2, I want to populate the corresponding cell in column B with "4".

If the number of months equals 1, I want to populate the corresponding cell in column B with "5".

If the number of months equals 0, I want to populate the corresponding cell in column B with "6".

Example:

Today's Date ("date_today") = 22 Sep 07

Column A.................Column B
("future_dates")......("eval_window")
31 Mar 09.................>6
29 Feb 08..................1
30 Jun 07..................Overdue
30 Nov 07..................4
31 Dec 07..................3
30 Sep 07..................6

I hope that all makes sense. Thanks for the help.


Hi,

I need to run my macro to filter any dates greater that today. I was running it as below but on a Friday it will look for Sarturdays date, i need it for Monday. What i need to do is look for any dates greater than today but am struggling to get it to work...

Range("A1:O" & Cells(Rows.Count, "B").End(xlUp).Row).AutoFilter Field:=8, Criteria1:="=" & Format(Now() +1, "dd/mm/yyyy")

Thanks

Blunder


I am really getting stumped on this one. I am using excel 2007 and everytime I try to write an IF statement using 5 variables, I get a formula error.

I need an If statement that will basically take a total in a cell and display an output depending on the range. For example, if it is less than or equal to 17.99 it will show BT, greater than 18.00 or equal to 20.00 it will show OT-, greater than 20.01 or equal to 22.00 it will show OT, greater than 22.01 or equal to 24.00 it will show OT+ and finally greater than 24.01 it will show AT.


Goodmorning Everyone,

I am trying to accomplish the following:

1. Use the auto filter and filter on the "TS-SO-ISSUED" column
2. Filter to get everything greater than 1 year using (format is mm/dd/yyyy)
3. Cut this data and paste it in a new worksheet
4. Repeat steps 1-3 for anything greater than 6 months but less than a year.

As clarification the 3 worksheets would end up being divided into
1. Less than 6 months
2. 6 to 12 months
3. Greater than 1 year

So far I created and complied from other macros this code. It runs without error, however nothing appears to happen. I believe when the filter is running it is not recognizing any values. Any ideas, I'm really running low here? Thanks!


Sub AutoFilterCopyPaste()
Const CutCopyMode = xlCopy 'xlCut / xlCopy
Dim wks As Worksheet
Dim Eio As Worksheet
Set Eio = ActiveSheet
Dim strSourceAddress As String

With ThisWorkbook
On Error Resume Next
Set wks = .Worksheets("Less Than 6 Months")
If Not wks Is Nothing Then
.Worksheets("Less Than 6 Months").Cells.Clear
Else
With .Worksheets.Add
.Name = "Less Than 6 Months"
End With
End If
Set wks = Nothing
Set wks = .Worksheets("6 to 12 Months")
If Not wks Is Nothing Then
.Worksheets("6 to 12 Months").Cells.Clear
Else
With .Worksheets.Add
.Name = "6 to 12 Months"
End With
End If
Set wks = Nothing
Set wks = .Worksheets("Greater than 12 Months")
If Not wks Is Nothing Then
.Worksheets("Greater than 12 Months").Cells.Clear
Else
With .Worksheets.Add
.Name = "Greater than 12 Months"
End With
End If
Err.Clear: On Error GoTo -1: On Error GoTo 0
End With
With Eio
strSourceAddress = .Cells.Find("TS-SO-ISSUED").CurrentRegion.Address
Application.Goto .Cells(1, 1)
.AutoFilterMode = False
If CutCopyMode = xlCopy Then
.Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TS-SO-ISSUED").Column, _
"=date(year(today()),month(today()-6),day(today()))", xlAnd, "date(year(today()),month(today()-12),day(today()))", xlAnd
.Range(strSourceAddress).Copy ThisWorkbook.Worksheets("Greater than 12 Months").Cells(1, 1)
Else
.Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TS-SO-ISSUED").Column, _
"=date(year(today()),month(today()-6),day(today()))", xlAnd, "date(year(today()),month(today()-12),day(today()))", xlAnd
.Range(strSourceAddress).Cells(1).Offset(1).Resize(.Range(strSourceAddress).Rows.Count - 1, _
.Range(strSourceAddress).Columns.Count).SpecialCells(xlCellTypeVisible).Cut ThisWorkbook.Worksheets("Greater than 12 Months").Cells(1, 1)
End If
.AutoFilterMode = False
End With

Set wks = Nothing
strSourceAddress = vbNullString

End Sub


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


Hi,

I am trying to do conditional formatting. I have a column set to format a cell with red fill if the cell is greater than or equal to 3. The cells have "if" statments in them as follows =if(E17=0,"-",(F17*D17)/E17). For some reason, excel is highlighting cells with a "-" in addition to those greater than or equal to 3. How can I set it up to only highlight cells greater than or = to 3


I would be grateful for any help on the following:

I have spreadsheet which contains several dates for different reasons.

I am trying to the change the colour of the dates (repeated down columns) if the dates have expired, the dates are older than 12 months or older than 3 months.

I have managed to use the formula in conditional formatting "less than =TODAY()-365."

Would be grateful for any help of a formula using 1 year instead of 365 days and also for 3 months

Many Thanks


Is it possible to use a greater and less than to count number of times Jan, Feb, March etc appear in a column, like greater and equal to 1st january and less or equal to 31st January and so on through the months, or maybe a simpler calcuation can be used

I tried using:-

=COUNTIFs($A$1:$A$500,">="&DATE(2010,1,1))-COUNTIFs($A$1:$A500,"

I'm having a difficult time with finding aa formula that fits my criteria.
I want to take one cell (A1)and show whether that cell is equal to or greater than another cell (B5),if the cell(A1) is greater than or equal to the other cell(B5) whats the difference totaled in cell (C5). If cell (A1) is equal to (B5) it should equal 0. If A1 is greater than B5 show the difference


hi
how to use match function to list all row# greater than equal to reference value.
eg:- sheet2! a:a contains date in dd/mm/yyyy formatt and have no of dates from a1 to a5000(few dates might appear more than 1 time).

requirement in sheet1:- a1=date

from b1 list all date range row# greater than equal to a1 value( including repeated dates).
many thanks in advance


I am trying to develop an IF statement for the following argument:

If 'cell' is less than or equal to 499, then 30. If 'cell' is greater than or equal to 500 but less than or equal to 799, then 40. If 'cell' is greater than or equal to 800 but less than or equal to 999, then 55. If 'cell' is greater than or equal to 1000 but less than 1499, 65. If 'cell' is greater than 1500, then 70.

I used to have this formula but lost it. It was created for me by a coworker who I no longer work with! I understand the basic IF formula but this gets too complex for my Excel knowledge.

Any help would be greatly appreciated.

kstrehle




I have a simple spreadsheet TBH for which two columns of data have date fields:

End date and Reminder date

I would like to set a conditional format using the icon sets for the reminder date as follows:

1) If the reminder date is less than 90 but greater than 60 days of the end date then show me a green icon

2) If the reminder date is less than 60 but greater than 30 days of the end date then show me a amber icon

2) If the reminder date is 30 days or less than end date then show me a red icon

I have come across ways of using today and dates in advance but not before...please can someone help me.?

I am trying to set up some Conditional Formatting so a totals cell will turn red if the value of the cell is greater than 170. Try this:

Cell A1: 170
Cell A2: 1
Cell A3: 5
Cell A4: =if(iserror(A3/A2),"",A3/A2)
Cell A4 has Conditional Formatting that says If Cell Value Is / Greater Than / =A1 and the format is a red pattern.

Everything seems to work fine as long as there is a value in cell A2. If you take that out then there is nothing in cell A4 but it is RED. The value in A4 is nothing (""). If "" is less than 170 then the color shouldn't change.


working on excel 2003 xp

I have dates in A and B column and in column C I have a formula that states that if date B is equal to date A then mark the column "ON TIME", if greater than 7 days difference then "FAILED". I have conditional formatting set so that "on time" would mark the cell green and "failed" mark the cell red. I have extended the conditional formatting to other rows that do not have dates in yet (as I will auto populate the cells with dates), but the conditional formatting is being applied (as the formula is also present), and is marking the cell "on time" even though there are no dates yet. How can I prevent this without resorting to deletion of the formula in the cell or the deletion of the conditional formatting?


I have a column of dates ranging from blank to future dates. I want to apply conditional formatting based on comparison to today's date. I'm working in Office 2007.

Using the stoplight icons with the checkmark, exclamation point, and ex symbols, I want the following results:

Green: value >= (today's date + 2 months)
Yellow: value > today's date < (today's date + 2 months)
Red: value



Folks, quick qustion on conditional formatting (Excel 2010)

I want to show deadline dates less than 3 months from today as being red
Between 3 and 6 months as being amber

I have created a cell with =Today() to act as the date to compaire against the deadline date.

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!


My boss wants to color code dates based on certain date ranges and have them update automatically when the document is opened. (This is to show whether an employee's qualification is current, due to expire within 6 months, due to expire within 1 month, and expired.) Is there any way to do this? I've been experimenting with the Conditional Formatting in the Format menu, but I haven't been successful.

The criteria I've been using is:

(Within 6 mos)
Condition 1: Cell value is between ="Today()+31" and ="Today()+180"

(Within 1 mo)
Condition 2: Cell value is between ="Today()"+1 and ="Today()+30"

(Expired)
Condition 3: Cell value is less than or equal to ="Today()"


The dates listed in each cell are in the following format: 8/1/2008

All the cells are showing as expired.

Is there another way to do this or am I entering the formulas or dates incorrectly?

Thank you!




Hello All

I'm using MS Excel 2010 and have a query on conditional formatting.
I have a worksheet, for which I want to set a conditional formatting rule obeying the following conditions-
-find minimum value between cell A2 and B2, format the cell C2 as
Cond 1- If minimum of the compared value in A2 and B2 is greater than equal to 80 but less than equal to 100, fill C2 as green.
Cond 2- If minimum of the compared value in A2 and B2 is greater then equal to 60 but less than equal to 79, fill C2 as orange.
Cond 1- If minimum of the compared value in A2 and B2 is greater then equal to 50 but less than equal to 59, fill C2 as yellow.
Cond 1- If minimum of the compared value in A2 and B2 is less than equal to 49, fill C2 as red.

Also, how to copy this rule for all the rows below.
Kindly refer the attached file for more.

Hello everyone!

I consider myself an intermediate excel user and am working on a new spreadsheet.

Essentially I have a sheet (see attached) in which I'm trying to track current and delinquent payments for an Home Owners Association. What I envision is under each monthly heading, a dollar amount owed is manually entered on a monthly basis. Each month, each unit is charged an 'assessment' which covers costs of the HOA expenses and common area maintenance and repair. I'm trying to provide a visual representation of what the current status of each unit is.

I would like some advice/help to do the following:

The cells containing the late payment, the unit number and name to be highlighted as follows using conditional formatting for 97-2003 compatibility:
No formatting for a non-zero that is less than thirty days past todays date (top left corner of the spreadsheet) green for a non-zero number that is greater than 30 days but less than or equal to 59 from todays date yellow for a non-zero number that is equal to 60 or greater, but less than 90 days past today's date red for a non-zero number that is greater than or equal to 90 days past due from today's date
Is this possible? Is there a better/easier way?

Thanks for any help in advance!

Kevin


Hi,

I have a conditional formatting issue.

I would like to change the colour of a cell depending on it's value. Easy enought, right?

Well, in this case it would be, if the value of the cell is less than, or greater than 5, but I want it to look at two other cells to determine the cell value.

eg: if cell y minus cell x is less than 5 then green
if cell y minus cell x is greater than 5 then red.

I am working on a staff roster with meal breaks.
Cells are formated in time (hh:mm) as below:

A1..........b1...........c1.........d1.........e1
............Start.......finish......start.......finish
Worker...10:00......12:00.....13:00......18:00

so I was hoping to get if the difference between d1 and e1 is greater than 5 hours then red (bad, costs in penalties), if equal to or less than 5 green (good, no costs)

Hope all that helps.
Can't wait to hear a response.
Thanks in advance.
GG