Conditional formatting - todays date

[joshbrfc]

Hello,

I want to conditional format this column N and everything below the expiry, so N3 downwards

I will be copy and pasting some dates in there and i want the cell to either be green(date is further away) or Red/Orange if close by or imminent. The automatic colour scale ideally as it would just be easier!!?


The idea is this is a prompt for our batches and when the expiry is close or.... has potentially expired. You have that visual prompt.

 

[Joe4]

I am not sure your question or data structure is 100% clear.
Are you trying to compare the dates in column N to the date in cell N1?
You mentioned if the date is close - how close do you want to check (specific number of days, please)?

But note one thing, if you are copying/pasting the values in to the cells that you want to color, you will need to use "Paste Values Only" into those cells, or else you will overwrite your Conditional Formatting Rules!

 

[joshbrfc]

I am not sure your question or data structure is 100% clear.
Are you trying to compare the dates in column N to the date in cell N1? Yes as N1 is todays date.
You mentioned if the date is close - how close do you want to check (specific number of days, please)? It doesn't really matter, within a week maybe. But its not important, whats important is the dates the furthest out being Green and then the dates closer being Red. And in between that Orange/Light green etc

But note one thing, if you are copying/pasting the values in to the cells that you want to color, you will need to use "Paste Values Only" into those cells, or else you will overwrite your Conditional Formatting Rules!

Hi, and thanks for the reply. See above.

 

[Joe4]

I think maybe you want to use Gradient Color or Color Scales in Conditional Formatting.
See here: Color scales in Excel: how to add, use and customize

If that does not help you, please show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[joshbrfc]

I think maybe you want to use Gradient Color or Color Scales in Conditional Formatting.
See here: Color scales in Excel: how to add, use and customize

If that does not help you, please show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I did try a 3 colour scale to begin with, but but it wasn't quite what i wanted. I kinda wanted a formula based off another cell.

Thanks for the replies though, i will check.

 

[Joe4]

I did try a 3 colour scale to begin with, but but it wasn't quite what i wanted. I kinda wanted a formula based off another cell.

Thanks for the replies though, i will check.

Well, you would have to decribe the details of how you would expect those formulas to work - right know you just appear to be providing us with generalities, no real details.
That is why I asked for you to show us some sample data and expected output, and explain how you got there (the logic you are trying to apply).
To build formulas requires concreter logic/rules you can program to.