Need formula help with automatic monthly column coloring based on flexible dates

Dear Excel Friends,

I need help on a very flexible formula for monthly automatic column coloring based on a date.

For everyone's reference, I have attached the Excel workbook Test5.

Points to be noted:

Sheet 1 is the data sheet.
Sheet 2 shows as example how the data should look visually as of 1st of February 2013.

Based on the any particular set day of the month (irrespective of any month and of any year) in system date (G1) and the corresponding date from pre - set date ( B3-O3), the colors of the columns in the range from B3-O28 need be automatically filled every month with: No color (previous month), Green (current month) , Red (next month) and Yellow for the rest of the months till the end.

Kindly note: Sheet 2 is a visual output reference example of how the formula should work with reference to the month February 2013.

Hope my query is clear.

Thanks everyone.

EL

you could use conditional format

=IF(MONTH(TODAY())=MONTH(C3), true,false) for green current month

=IF(MONTH(TODAY())+1=MONTH(C3), true,false) for red next month

=IF(MONTH(TODAY())+2<MONTH(D3), true,false) for yellow all month

i'll edit and add an example

Dear Friends.

Thanks for the kind response. I dont know maybe I dint make myself clear enough. What I want is say for eg. every 15th of the month of ANY year 2013, 2014, 2015 etc, I want in the select column range, the current month column data set turn green, the previous month -no color, the next month -red color and all columns succeeding the red month -turn yellow.

So the condition of the formula is day as well as month specific and not year specific.

So for any set given date eg. say when in the column range date = 3/15/2013, when it coincides the system date for eg. say 3/15/2013, the colors for the columns under 3/15/2013 turns green, 2/15/2013 turns no color 4/15/2013 turns red and all columns after 4/15/2013 ( 5/15/2013 - what ever the end range) turns yellow.

The cycle continues every month.

Kindly note in the attached Text 5.xls sheet , the column date to be compared with system date is set from 1/1/2013 -2/1/2014 but like I said the formula need to be flexible so that it works on any day 1, 5 or 15 or any other day of the month and for any year not just 2013.

Sorry if I did not make myself absolutely clear.

I tried both the suggestions of the experts above but the formula is not working for some reason.

you example has a month per column - would that still be the same and just a different day - or do you have different columns for the dates
so a column for the 1st a column for the 5th
or just 1 column per month - but the day can be different

aussming column B has 15/1/13 and then column B has 5/2/13
and today is the 14/1/13
what colours will apply

When the todays date is 15/1/13 then it applies the column colours

Column A = 15/1/13
column B = 15/2/13

so the colours will only be apply when today is between dates 15/1/13 to 31/1/13
and then on the 1/2/13 - no colours until today reaches 15/2/13

Hi,

Yes you are absolutely right. One Column per month but can be of any day and that date would be uniform between the different column months. Not different days in the dates in different columns.

First assume all the dates are in American System and given in the column B3- O3. They have uniform day for each month i.e. from 1/15/2013 - 2/15/2014. This example is just a variation from that given in the worksheet 5 which is from 1/1/2013-2/1/2014.

So following to your question, Lets take today as the example. Today is March 25, 2013 as system date (G1). Hence when the date comparison is done between the system date in G1 with the set monthly column dates in (B3-O3), The coloring pattern as of today March 25, 2013, Column D(D3:D28) would be green (as the month is March and the day in the date string has already crossed 15th for that particular month but not yet reached the 15th of the following month ) Column E would be red (i.e. the next month ; April) and all columns from F (May 2013) to O (Feb 2014) (F-O 3: F-O 28) would be yellow.

This formula would automatically continue forwards the next month. i.e. on April 15th 2013 Columns B, C and D (previous months Jan -Mar 2013) would have no colour Column E would be green (April 2013), Column F (May 2013) would be Red and Column G-O (June 2013- Feb 2014) would be Yellow.

Hope I have made it clear now and not made it more complicated in my narrative.

Thanks in advance.