Conditional formatting cells by due date usin tip054

Lewis

Board Regular
Joined
Feb 16, 2002
Messages
50
I have used the method in http://www.mrexcel.com/tip054.shtml
to set up cells showing due dates. However when a cell contains nothing it defaults to the first conditional format (red). Is there a way to use the last available conditional format statement to make empty cells colourless and any other date a third colour say yellow
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
mod for empty cell:

=and(len(e2),(E2-TODAY())<6)

change references to suit

paddy
 

Lewis

Board Regular
Joined
Feb 16, 2002
Messages
50
That does not seem to work still get the red background for empty cells
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
post the formulas for the conditional format(s) you currently have...

paddy
 

Lewis

Board Regular
Joined
Feb 16, 2002
Messages
50

ADVERTISEMENT

They are exactly as in tip054
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
with cell e2 blank,

=AND(LEN(E2),(E2-TODAY())<6)

returns false, whereas

=(E2-TODAY())<6

returns true, because at today's date,

=E2-TODAY()

returns -37555. Reviewing the tip you mentioned, the suggestion solution should work, give that CF applies the format of the first true condition of the set...maybe check the formulas again, as CF has an odd habit of sticking quote marks round the formulas for no obvious reason.
paddy
 

Lewis

Board Regular
Joined
Feb 16, 2002
Messages
50
Works fine and leave me with the third condition which I set to =LEN(E2)>1 to give me a different colour for any other date
 

Forum statistics

Threads
1,144,770
Messages
5,726,187
Members
422,661
Latest member
foxleinlady

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
Top