Conditional formating with dates

Twizted

New Member
Joined
Nov 1, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. Web
i have a spreadsheet we are using to track jobs column f has estimate dates and column H has PO dates i would like the cells in column H to hightlight different colors for 14 days 21 days and 30+ days from which the Po was receives from the date the estimate was sent. not all cells have dates in them yet so i would like to keep that cell white. right now my formula is =(h2-f2)<=14 will be green second rule is =(h2-f2)<=21 will be yellow and finale rule is =(h2-f2)<30 is red. this seems to work but it changes cell colors if nothing is there how can i stop this?
 

Attachments

  • excel help.PNG
    excel help.PNG
    87 KB · Views: 10

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
just a quick reply -
it will be comparing some empty cells and do uses that as 0
=(h2-f2)<=14 will be green second rule is =(h2-f2)<=21 will be yellow and finale rule is =(h2-f2)<30 is red.
you often have to identify the blank cells
will the SO nu also be blank

but what you need is an AND( H2<>"" , (h2-f2)<=14 )
so you test to make sure h2 is NOT Blank
OR
AND( OR(F2<>"",H2<>"" ), (h2-f2)<=14 )
so if either is blank

But of the dates are only blank when another cell is ALWAYS blank - then use that

In you example the only dates that are blank are when I is blank
so
AND( I2<>"" , (h2-f2)<=14 )
etc


Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Upvote 0
Solution
thanks for the quick reply AND( H2<>"" , (h2-f2)<=14 ) does what im looking for
 
Upvote 0
excellent
you are welcome
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top