Search : in
By :

Excel Formula for Conditional Formatting

Last answer on Sep 30, 2009 1:32:47 am BST Scott, on Sep 28, 2009 6:53:49 pm BST 
 Report this message to moderators

Hello,
I'm trying to find a formula and instructions for shading date values in a column on a worksheet based on the month relative to today's date.
For example, I would like for all dates occuring in the current month or previous months to be shaded in red; all dates occuring in the the next two calendar months to be shaded in yellow and all other future dates to be shaded green.

Thanks in advance for any help provided.

Configuration: Windows Vista Internet Explorer 7.0, Excel 7.0

Best answers for « Excel Formula for Conditional Formatting » in :
Remove blank cell from conditional formatting Show
Conditional formating - apply on more cells Show
Avoid duplicates in Excel Show Avoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Unlimited Conditional Formatting ShowUnlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
General Terms and Conditions of Use ShowAll Rights Reserved - 2009 - Communitic International Object These General Terms and Conditions are meant to define the ways in which the services of the website en.kioskea.net, hereafter "The Service," are made available, as well as the...
Ask your question Reply

1

venkat1926, on Sep 29, 2009 2:16:21 am BST
  • +1

In the conditional format window of cell A1
choose under conditon 1
formula is
and the following formula

=OR(MONTH(A1)=MONTH(TODAY()),MONTH(A1)=MONTH(TODAY())-1)

color pattern is red

in the second condition similarly the formula is

=OR(MONTH(A1)=MONTH(TODAY())+1,MONTH(A1)=MONTH(TODAY())+2)
color patter is gsreen

if this is ok confirm "yes"

copy A1 down edit-pastespecial format only.

see the logic of the formulas

   
Reply to venkat1926

2

Anonymous, on Sep 29, 2009 2:20:03 pm BST
  • +1

Awesome!

Thanks so much, I do see the logic in the formulas, and I did tweak the formulas a little so that I could add a third condition.

I have one question though, how can I make the formula apply to the year in the date as well? For example the formula in condition 2 would apply to not only the next two months of this year, which is the desired outcome, but would also apply for all other dates in the months of october and november in previous years.

Thanks again

   
Reply to Anonymous

3

 venkat1926, on Sep 30, 2009 1:32:47 am BST

I SUPPOSE that the same formula may work.

see the logice
=MONTH(TODAY())+1
gives 10 that is October. It does not indicate any year value.

try it

but remember in excel versions upto 2002 there can be only 3 condition in conditional formality. I think this restriction is removed in excel 2007

   
Reply to venkat1926
Ask your question Reply
They need your help