Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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







I have a date in cell A2. In cell B2 I want to return A,B or C
If the date is less than 2 months =A
If the date is greater then 2 months but less than 6 months =B
If the date is greater than 6months old then =C

If the months is a problem then I could use
less than 61 days =A
Greater than 60 but less than 181 =B
Greater than 181 =C

Hope this is clear.
Thanks in advance.


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

In A1 I have a date e.g. 20/10/2011

In A2 I have today's date

How can I apply conditonal formatting to A2 so that if the difference between the 2 dates is greater than 3 months, I get the cell to go red.


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

In 2003, I'm trying to create a conditional that if today is greater than or equal to the date in the cell, highlight the row red.

Usually conditional formatting works just fine. However, I'm applying this to a lot of rows that don't have information yet. So, due to the fact that the cell has nothing, obviously TODAY() is greater than or equal to nothing. Therefore, highlighting the row red.

Is there a way around this? I don't a bunch of red rows.


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.


Hello Everyone,

I apologize for the length, but figuring this out will literally save me a great deal of time each week. Please, take a look and off up your suggestions, maybe you're the genius that can fix this since no one else seems to be able to.

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 the following code. It runs without error, however nothing appears to happen.

I believe when the filter is running it is not recognizing any values I am searching for. Is the syntax wrong on my dates? I can get it to work on other data sets if I filter for less than or greater than a number, but the dates code seems to evade me. Any ideas, I'm really running low here, and the pressure is on? Thanks in advance for any help!

Code:

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 
  




Please help. Thank you everyone.

I can post a sample data set if there is any confusion.



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.


Hello -

I am using Excel 2003 and I'm attempting to use conditional formatting to format my spreadsheet but I need to have more than 3 conditions. I'd like to know how to do this with VBA.

I've attached an example of my spreadsheet I've been having trouble with.

I have to subtract today's date from the dates in the "Date Payment Required" column. If the difference is less than or equal to 9, but greater than or equal to 0, then the entire row should be colored yellow. If the difference is less than 0 (i.e. overdue), the entire row should be colored red. The formatting should only happen to a row when there is a date entered under column L.

If a date has been entered into the "Date Payment Received" column, the formatting for the above should then return to normal.

I also need to subract today's date from the dates in the "Check Status of Equip" column. If the difference is less than or equal to 16, but greater than or equal to 0, the entire column should be colored pale blue.

Thank you for the help!


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




This function only uses two cells, E2 & D2. I need an if function that asks is cell E2 greater than or equal to 500000 and if cell D2 is BR then it is platinum, if less than 500,000 but greater than or equal to 250,000 and is BR then it is Diamond, if less than 250,000 but greater than or equal to 100,000 and is BR then it is Gold, if less than 100000 but greater than or equal 50000 and BR then it is Silver, If cell E2 is greater than or equal to 250000 and cell D2 is CD then Platinum, If less than 250,000 and greater than or equal to 100,000 and it is CD then it is Diamond, If less than 100,000 but greater than or equal to 50,000 and CD then it is Gold, If less 50,000 but greater than or equal to 15,000 and CD then it is Silver, If greater than or equal to 150,000 and DR then it is Platinum, If less 150,000 but greater than or equal to 50,000 and DR then it is Diamond, If less than 50,000 but greater than or equal to 15,000 and DR then it is Gold, If less than 15,000 but greater than or equal to 7500 and DR then it is Silver, If greater than or equal to 100,000 and ENT then it is Platinum, If less than 100,000 but greater than or equal 25,000 and ENT then it is Diamond, If less than 25,000 but greater than or equal to 15,000 and ENT then it is Gold, If less than 15,000 but greater than or equal to 7500 and ENT then it is Silver. How would this function look?

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


Within each row of my spreadsheet there are several columns which contain dates to which i would like to apply some conditional formatting.

There is one column which contains a number which represents a number of months.

I would like the background of the cells containing dates change colour reflect whether the date falls within the previous x number of months from the current date where x is the number of months in the previously mentioned column.

eg.

cell A1 contains the number 6 to represent 6 months
cell B1 contains a date which is within the last 6 months
cell C1 contains a date which is prior to the last 6 months

the conditional formatting should set the background colour of cell B1 to green and the background colour of cell C1 to red

I have found that there is a specific rule for something similar to this already within the conditional formatting but it does not have a very large range from the current date, nor does it reference another cell.


I am in need of a formulas in Cell O4 for the following:

100 in L4. 5 in N4. If L4 is less than or equal to 100 value to stay as N4. If L4 is greater than 100 but less than 500 O4 to show 80% of N4. If L4 is greater than 500 but less than 1000 O4 to show 70% of N4

in cell P4

25% of N4 if L4 is greater than 100 but less than 500

In cell R4

15% of N4 if L4 is greater than 500 but less than 1000

Therefore at less than or equal to 100 cell O4 is 100% of N4.
If L4 is greater than 100 but equal to or less than 500 cell O4 is 80% of N4 and cell P4 is 20% of N4.
If L4 is greater than 500 but equal to or less than 1000 cell O4 is 70% of N4 and cell P4 is 20% of N4 and R4 is 10% of N4.

I look forward to your help.


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




I'm trying to get the sum of a row in a pivot table IF the header row date is greater than a date I have entered on another tab.

Right now I have =sumif($B6:$Y$6, $B$4:$Y$4, ">Data!K3")

If the dates in B4:Y4 are greater than the date located in Data!K3, I want to add the totals of B6:Y6 under those corresponding headings of the pivot. Make sense? But it's totalling the entire row as is, rather than just the dates I want.

Basically I have a a pivot of data with the months as the column labels (Jan-12, Feb-12, etc.). I'm trying to add up each row's totals for the last 12 months (even though I have 20 months worth of data). I have October-13 in cell K3 on another tab labelled data.

Any ideas?

I am trying to make a row change color when the last column date is equal or greater than today. I have made it change color but it erased all my information.

the cell i'm basing the row changes on has conditional formatting to black if 0 or to red if the date is "greater or equal to =TODAY()".

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

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