conditional formatting help...please!!!

jever96

New Member
Joined
Oct 7, 2006
Messages
2
I'm requesting help for conditional formatting. Here's the scenerio...
It seems relatively simple but... I'm tracking dates. I type a date (7-Jan-06) into a cell, depending on the date, it needs to be white, yellow or red.

1. Dates between 335-364 days from the date entered into the cell need to be yellow (fill).
2. Dates older than 365 days from the date entered into the cell need to be red (fill).

I'm new to excel and have been learning but this has stopped me in my tracks. Help! :(
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Assuming column A, change as necessary

Formula 1:
Code:
=(TODAY()-$A1>365)*($A1<>"")

Formula2:
Code:
=(TODAY()-$A1>334)*($A1<>"")


You also didn't say what you wanted to do if it was 365 days.
If you want that included in the first formula, change it to this:
Code:
=(TODAY()-$A1>364)*($A1<>"")
 

jever96

New Member
Joined
Oct 7, 2006
Messages
2
Help...thank you

Thank you very much...this works great. Thank you again!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,448
Office Version
  1. 365
Platform
  1. Windows
jever96

Welcome to the Mr Excel board!

You say that you are new to Excel, so here is a little additional information that may be of use in this or future projects.

The TODAY() function is a 'volatile' function which calculates more often than the 'standard' Excel functions. If you use it a lot in a sheet (for example if you have a lot of cells that are using this Conditional Formatting which uses the formula twice in each cell) then you may find the performance of the sheet deteriorate markedly.

One way to overcome that would be to put the formula =TODAY() in a cell somewhere out of the road (maybe even in a hidden column) and then refer to that cell in your formulas.

For example, if =TODAY() was in cell Z1, then your Conditional Formatting formulas as suggested by HOTPEPPER would become:
Code:
=($Z$1-$A1>365)*($A1<>"")
and
=($Z$1-$A1>334)*($A1<>"")
This would mean that TODAY() was only calculating once on the sheet but your Conditional Formatting formulas would still produce the same results.
 

Forum statistics

Threads
1,136,697
Messages
5,677,270
Members
419,683
Latest member
MrVBAConfused

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