Conditional Formatting Dates Greater Than 6 Months... 


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 ...
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 ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
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
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.
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
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 TODAY270
Cell value is between Today270 and Today365
and
Cell value is greater then Today365
Any help is appreciated...
thanks for any help you can provide.
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 TODAY270
Cell value is between Today270 and Today365
and
Cell value is greater then Today365
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.
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.
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 "TSSOISSUED" 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 13 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:
Please help. Thank you everyone.
I can post a sample data set if there is any confusion.
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 "TSSOISSUED" 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 13 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("TSSOISSUED").CurrentRegion.Address Application.Goto .Cells(1, 1) .AutoFilterMode = False If CutCopyMode = xlCopy Then .Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TSSOISSUED").Column, _ "=date(year(today),month(today6),day(today))", xlAnd, "date(year(today),month(today12),day(today))", xlAnd .Range(strSourceAddress).Copy ThisWorkbook.Worksheets("Greater than 12 Months").Cells(1, 1) Else .Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TSSOISSUED").Column, _ "=date(year(today),month(today6),day(today))", xlAnd, "date(year(today),month(today12),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 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.
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!
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 "TSSOISSUED" 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 13 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("TSSOISSUED").CurrentRegion.Address
Application.Goto .Cells(1, 1)
.AutoFilterMode = False
If CutCopyMode = xlCopy Then
.Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TSSOISSUED").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("TSSOISSUED").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
I am trying to accomplish the following:
1. Use the auto filter and filter on the "TSSOISSUED" 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 13 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("TSSOISSUED").CurrentRegion.Address
Application.Goto .Cells(1, 1)
.AutoFilterMode = False
If CutCopyMode = xlCopy Then
.Range(strSourceAddress).AutoFilter .Range(strSourceAddress).Find("TSSOISSUED").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("TSSOISSUED").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
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 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
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 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 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 (Jan12, Feb12, 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 October13 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!
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
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
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
Hi Guru's
I would like to know if the following is possible, I've been trawling the forums using the search function, but I can seem to pin down an answer.
I have a workbook containing the daily holiday records in hours of staff members (names in col A starting at row 2), the first six months are on sheet 1, the second six months on sheet 2, with each column representing a date (dates in row 1 starting at column B).
What I would like to achieve is by employing a UserForm to populate my Textboxes with the dates and hours taken as holiday, so returning anything with a value greater than 0 also ignoring blank cells.
The staff member lookup i am currently using is a combobox from the names in column A. I can return to a textbox information from a range but i have been unable to only select the values greater than 0. I have also been unable to show the dates with values greater than 0
Hope you can suggest a solution
Regards
S
I would like to know if the following is possible, I've been trawling the forums using the search function, but I can seem to pin down an answer.
I have a workbook containing the daily holiday records in hours of staff members (names in col A starting at row 2), the first six months are on sheet 1, the second six months on sheet 2, with each column representing a date (dates in row 1 starting at column B).
What I would like to achieve is by employing a UserForm to populate my Textboxes with the dates and hours taken as holiday, so returning anything with a value greater than 0 also ignoring blank cells.
The staff member lookup i am currently using is a combobox from the names in column A. I can return to a textbox information from a range but i have been unable to only select the values greater than 0. I have also been unable to show the dates with values greater than 0
Hope you can suggest a solution
Regards
S
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
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
Hi
I am trying to get the following of function working
=IF(D13IR4,"Professioanl",IF(D13>IR3,"European","Higher than IR2")))
when i put it in this way only the peofessional and amateur work
i would like the following to happen
if the number in D3 is less than cell IR5 it should say Amateur
If it is greater than or equal to cell IR5 but less than IR4 is should say professional
if it greater than or equal to IR4 but less than IR3 it should say magners
If it is greater than or equal to IR3 but less than IR2 it should say european
and if it is greater than or equal to IR2 it should say workd class.
can anyone help
I am trying to get the following of function working
=IF(D13IR4,"Professioanl",IF(D13>IR3,"European","Higher than IR2")))
when i put it in this way only the peofessional and amateur work
i would like the following to happen
if the number in D3 is less than cell IR5 it should say Amateur
If it is greater than or equal to cell IR5 but less than IR4 is should say professional
if it greater than or equal to IR4 but less than IR3 it should say magners
If it is greater than or equal to IR3 but less than IR2 it should say european
and if it is greater than or equal to IR2 it should say workd class.
can anyone help