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

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

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

Multiple Conditional Formatting Rules for a Cell in Excel

Apply multiple Conditional Formatting rules to a cell or range in Excel. This allows you to change the way a cell ...

Apply multiple Conditional Formatting rules to a cell or range in Excel. This allows you to change the way a cell ...

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

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## 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, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

Hi there

Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours - I want have an IF formula to say where 02.25hrs > 0.40 then type "FAILED" but I cant overcome the greater than problem on the hours which is giving me the wrong answer.

Please can someone help me. How do I create an IF formula to find cells where the number of hours in one cell is more than the number of hours in another cell? eg one cell has 0.40 hrs and another has 02.25 hours - I want have an IF formula to say where 02.25hrs > 0.40 then type "FAILED" but I cant overcome the greater than problem on the hours which is giving me the wrong answer.

What formula would I use if the total sum of a column was greater than 4.00 then I would minus 0.35. If not minus nothing.

Thankyou

Thankyou

Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I was able to convert start date and and end date to the number of days

inbetween the two dates. Does anyone know how to then convert the number of

days into months?

inbetween the two dates. Does anyone know how to then convert the number of

days into months?

I have imported a DBF file into Excel and have a column of dates that are missing the leading zero on single didgit months. When I try to us the custom format of mm/dd/yyyy it doesn't work (interestingly, after I select that format if I click on an individual cell it changes to the right format).

Does anyone know a better way to do this?

Thanks in advance!

Does anyone know a better way to do this?

Thanks in advance!

I see a formula that our Human Resources Department is using to annualize voluntary turnover.

It looks like this:

=(E3/((A3+D3)/2))*12/months

So if I substitute with some actual data:

=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).

What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).

It looks like this:

=(E3/((A3+D3)/2))*12/months

So if I substitute with some actual data:

=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).

What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).

Hello,

My Cell C3 is a numeric value.....I have set conditional formatting to

make the text red and bold when the number is equal or less than 10,000

is there a way I can make cell E3 display a message when the C3

condition is true?? or if not a message is there a way to make a

message box pop up when my C3 condition of less then or equal to 10,000

is true?

how would I do something like this?

My Cell C3 is a numeric value.....I have set conditional formatting to

make the text red and bold when the number is equal or less than 10,000

is there a way I can make cell E3 display a message when the C3

condition is true?? or if not a message is there a way to make a

message box pop up when my C3 condition of less then or equal to 10,000

is true?

how would I do something like this?

I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()-A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completed-on date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)

This formula is supposed to calculate difference between today and date hired to give years and months of service. Works fine for all except if the person was hired in year 2000 then this formula shows 110 y. Any one know how to fix this? or have a different formula that works.

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks

Rick

=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"

Thanks

Rick

How can I make a cell or the font of the cell flash or blink? I assume that

it is a function of conditional formatting. I know it is possible because I

have seen it before but I do not know how to do it.

it is a function of conditional formatting. I know it is possible because I

have seen it before but I do not know how to do it.

I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example: If the call came in between 22:00 and 23:59 color is light green.

If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

Thanks in advance.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example: If the call came in between 22:00 and 23:59 color is light green.

If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

Thanks in advance.

I'm attempting to filter columns E, G, and I all at the same time. I need to

display rows that have values greater than 0 in any one of those three

columns. So far I can only filter progressively. If I filter column E and

then filter column G, the second filter is only applied to the results of the

first filter.

Does anybody know if it's even possible to do what I'm trying to do?

display rows that have values greater than 0 in any one of those three

columns. So far I can only filter progressively. If I filter column E and

then filter column G, the second filter is only applied to the results of the

first filter.

Does anybody know if it's even possible to do what I'm trying to do?

I have a spreadsheet set up with an employees information, including the start date. After each year that the employee is working, he gets bumped up on the pay scale. For this reason, I only need to know how many years the employee has been working, rounded down to the nearest year.

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!

This is what I have so far (hire date is in column B):

=((TODAY()-B4)/365)&" YEARS"

This function gives me a number with many decimal places.

I tried:

=ROUNDDOWN((TODAY()-B4)/365),0)&" YEARS"

It tells me I have too few arguments. Please help!

From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.

I have been using Excel (XP) to make a text chart for several months. Some of

my text entries are rather lengthy. The past two weeks these lengthy entries

are showing up as pound signs (#########) when I click off the cell. I know

the text will fit in the cell, and the problem isn't solved by making the

cell bigger or using a little bit less text. I have the cells formatted as

"text" and "wrap to fit". I have printed the pages and the printed version

also has pound signs. I just want my text to show up!

my text entries are rather lengthy. The past two weeks these lengthy entries

are showing up as pound signs (#########) when I click off the cell. I know

the text will fit in the cell, and the problem isn't solved by making the

cell bigger or using a little bit less text. I have the cells formatted as

"text" and "wrap to fit". I have printed the pages and the printed version

also has pound signs. I just want my text to show up!

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Very new in this board. If I post this question in the wrong forum please bear with me.

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions

Hello, please help me out with this one.

So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!

So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!

Is there a way I can use conditional formatting or something to change the color of the cell once I enter a value or text into that cell? For example if I currently have the A2 cell color as a gray color, and I enter information into A2 can I have it set to automatically change to no fill or any other color without having to click out of the cell, back into it, and then clicking the button?? Does that make sense??? Can someone help me?

Is there a way to calculate the average days between a column of dates?

Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

Thank you in advance!

~cp

Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

Thank you in advance!

~cp

Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!

I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.

Thanks in advance!

Hi,

I am looking to change how an active cell is viewed. For instance when you are looking at your spreadsheet you can see the active cell has a thin black border around it. I would like to change the border to a thicker one and change the color to blue or red. I am trying to make it easier to the eye so when you are working on a large spreadsheet it is immediatly apparent where you are to avoid squinting.

If anyone can help me please let me know.

I am looking to change how an active cell is viewed. For instance when you are looking at your spreadsheet you can see the active cell has a thin black border around it. I would like to change the border to a thicker one and change the color to blue or red. I am trying to make it easier to the eye so when you are working on a large spreadsheet it is immediatly apparent where you are to avoid squinting.

If anyone can help me please let me know.

Hi all,

I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!

I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.

I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)

Any help you can give would be much appreciated.

Cheers,

Kevin

I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!

I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.

I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)

Any help you can give would be much appreciated.

Cheers,

Kevin

I am a very advanced Excel user so this is a new one for me.

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series

Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series

Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!