Conditonal Format with quarter and year

[rgazzara]

Good morning,
I am using excel 2007 and I have a report that maintains when an inspection was last completed in the format of quarters and the year completed (32011=quarter 3 year 2011) . I have tried to set conditonal formatting to show that all previous quarters from the current quarter and year are red, the next 2 quarters are yellow, and 3 or more quarters out are green,but I've ran into some difficulty. I've also set up the todays date to be inserted in to the spread sheet and converted to the current quarter (=INT((MONTH(B1)-1)/3)+1&YEAR(B1). I've tried to use this cell as a base to compare to but the math doesn't work out and I am really stuck. Please see below for a example of the cells. If the current quater is 32011, then 42011 would be yellow (next quarter), 32012 would be green (greater then 2 quarters) and the 32010 would be read (past current quarter). I have dates ranging from 12006 to 42014.

NEXT MI
<TABLE style="WIDTH: 35pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=46 border=0><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 35pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=46 height=17>
42011


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 35pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=46 height=17>32012 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 35pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=46 height=17>32010 </TD></TR></TBODY></TABLE>


Any help on this would be greatly appreciated.

V/R
Rob Gazzara

 

[rgazzara]

P45,
You're absolutely correct, I forgot to change the formulas to reflect the correct position of the cells on the spreadsheet in the formula. Thank you. For some reason I can not post on color, so here it goes. The cells start in H1, adjust cell reference in formulas as necessary (As previously stated by P45cal). Here's the formulas

RED
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)>6

DARK BROWN
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)>4

LIGHT BROWN
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)>2

LIGHT YELLOW
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)>.9

BLUE
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)=0

GREEN
1. / Formula is =RIGHT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),4)*4+LEFT(INT((MONTH(TODAY())+2)/3)&YEAR(TODAY()),1)*1-(RIGHT(H1,4)*4+LEFT(H1,1)*1)<-0.1

These formulas will show current quarter in blue previous quarters in green and colors will change every 2 quarters, until red. I've also advanced the computers clocks and it works perfectly, the quarters change as required. I hope someone else may need to use this information, if so it will save them some time.

V/R
Rob