Odd Year Even Year

[Papi]

I have a worksheet that has 24 cells representing 2 years of Month cells. Every six months we have to change cables to match a color code that somehow works on Odd and Even years every six months. What it is supposed to do is turn the cells white for OCT NOV DEC on an Odd year and JAN FEB MAR on an Even year. Over two years there are four colors that follow the above pattern.

So if this was 2008 then OCT NOV DEC would be Orange and JAN FEB MAR of 2009 would be also be Orange and then 2009 APR MAY JUN and JUL AUG SEP would also be Orange.

Hopefully this is clear enough to understand. I suspect that if I can simply figure out how to capture being able to figure out how to identify Odd an Even years I should be able to figure out the rest.

Thanks in advance.

 

[jimrward]

here are some thoughts for starters, using conditional formatting, select the cells , choose just one row for starters, my example had dates in A1 to where ever and i highlighted B1 to P1

click on conditional formatting, change the first to formula is and paste in the following

=OR(AND(MOD(YEAR(A1),2)=0,MONTH(A1)>9),AND(MOD(YEAR(A1),2)=1,MONTH(A1)<4))

next click on format and then patterns and choose orange

this is my first stab, but something to get you going

 

[Papi]

Sorry...this should have read "So if this was 2008 then OCT NOV DEC would be Orange and JAN FEB MAR of 2009 would be also be Orange and then 2009 APR MAY JUN and JUL AUG SEP would be Blue.

 

[jimrward]

hang on we need to define some rules here as condition formatting only has 3 events/conditions you can trap otherwise we start stepping into VBA

the first one I have supplied used the odd/even year and whether it was last 3 months of even year or first 3 of odd year

questions

1) how many colours, white can be deemed as no action required

2) a rule that governs the blue cells you mention

3) would the cells be blue in an even year

 

[Papi]

Hello Jim,

Thanks. As luck would have it, one of the colors is white so that can be left as the default. Attached is an HTML snapshot of the cells they way they fall in place. Maybe that will help. Odd Year is JAN WED MAR (Orange), APR MAY JUN JUL AUG SEP (Blue), OCT NOV DEC (White), Even Year JAN FEB MAR (White), APR MAY JUN JUL AUG SEP (Green) and OCT NOV DEC (Orange). I did not use conditional formatting to enter the colors so I could get it back to you quickly. However it seems it did not (or does not) put the colors in anyway.

Lifting Gear Bi-Annual Colors.xls
	A	B	C	D	E	F
1	2009
2	Jan	Feb	Mar	Apr	May	Jun
3	Dec	Nov	Oct	Sep	Aug	Jul
4	2010
5	Jan	Feb	Mar	Apr	May	Jun
6	Dec	Nov	Oct	Sep	Aug	Jul
Lifting Gear Bi-Annual Colors

 

[jimrward]

ok, starting with 1/1/08 in A1 and copied across to 1/12/09
select B1 across

I have the following conditional formats

The original one as posted above colour = orange

click on ADD for the next condition and copy in the following as Formula Is and choose Green for the format colour

=AND(MOD(YEAR(A1),2)=0,AND(MONTH(A1)>3,MONTH(A1)<10))

click on ADD again and this is the last one with colour = Blue

=AND(MOD(YEAR(A1),2)=1,AND(MONTH(A1)>3,MONTH(A1)<10))

 

[Papi]

The way they change color codes is every six months it changes. This is what it would look like;

2009 (Odd year) Jan Feb Mar (Orange), Apr May Jun Jul Aug Sep (Blue), Oct Nov Dec (White)

2010 (Even Year) Jan Feb Mar (White), Apr May Jun Jul Aug Sep (Green), Oct Nov Dec (Orange)

2011 (Odd Year) (Odd year) Jan Feb Mar (Orange), Apr May Jun Jul Aug Sep (Blue), Oct Nov Dec (White)

2012 (Even Year) Jan Feb Mar (White), Apr May Jun Jul Aug Sep (Green), Oct Nov Dec (Orange)

The purpose is to make that cables are color coded and must be automatically changed out every six months. Therefore, the somehow the odd and even years come into play. I hope I am not confusing the description of how it works.

 

[jimrward]

the above conditional formats I have posted work for me and do exactly what you ask, if you send me a PM with your email address in I will send you what I have

 

[Papi]

Thanks very much Jim. That was a great layout and very well orchestrated on your part. The formulas and the formatting were perfect and gave us exactly what we needed. Your time and patience are greatly appreciated. Thanks again for your great efforts.