Quarterly date conditional format

[guppyguy]

I have a cell containing a date which I want to change to a different color if it is not in the current quarter (ie Q1 is 1 Jan thru 31 Mar, etc).

So using todays date (17 August), if the cell date is 1 July, it is green, but if is 30 June or once today is 1 Oct it turns red.

What is the easiest way to code this?

Thanks!

 

[ChrisOswald]

Format all the cells green using a normal format cell command, and then
try using this as your conditional format for the Out of quarter dates.

=INT((MONTH($A2)-1)/3)<>INT(MONTH(NOW())/3)

 

[guppyguy]

It works - thanks!

 

[Peter_SSs]

It works - thanks!

Are you sure?

It may not be possible with your data but note that will format blank cells and will mis-format certain dates, unless I have not understood your requirement?

For the current date, consider the dates shown, with the Conditional Formatting suggested. Are my comments in column B correct?

Excel Workbook
	A	B
1	Date
2	10/08/11	OK
3	15/11/11	OK
4		Should not be red?
5	30/06/11	OK
6	15/08/10	Should be red
7	1/07/11	OK
8	12/12/11	OK
9	29/09/11	OK
10	15/10/11	OK
11	27/09/12	Should be red
CF Qtr (Aug)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =INT((MONTH($A2)-1)/3)<>INT(MONTH(NOW())/3)	Abc

The situation in September will be worse. To demonstrate, I have put a Septemebr date in D1 and modified the suggested CF to refer to that cell rather than to the actual current date. I believe that most of the dates in column A (not the same dates as the previous sample) will be formatted incorrectly.

Excel Workbook
	A	B	C	D
1	Date			21/09/11
2	1/07/11	Should not be red
3	10/08/11	Should not be red
4		Should not be red?
5	30/06/11	OK
6	15/10/10	Should be red
7	1/12/11	Should be red
8	17/11/13	Should be red
9	25/09/11	OK
10	27/10/11	Should be red
11	27/09/12	OK
CF Qtr (Sep)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =INT((MONTH($A2)-1)/3)<>INT(MONTH($D$1)/3)	Abc

If my understanding is correct, then here is one way that may be suitable. I have placed date formulas in D1 and D2 to make the CF formulas simpler as they don't have to calculate those values in every cell's CF.

Excel Workbook
	A	B	C	D
1	Date			1/07/11
2	10/08/11			30/09/11
3	15/11/11
4
5	30/06/11
6	15/08/10
7	1/07/11
8	12/12/11
9	29/09/11
CF Qtr

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =OR(AND(A2<>"",A2<$D$1),A2>$D$2)	Abc

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;">Quart.</td><td style="font-weight: bold;text-align: center;background-color: #FF0000;;">CF</td><td style="font-weight: bold;text-align: center;;">Today is</td><td style="font-weight: bold;text-align: center;;">Quart.</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FF0000;;">1/1/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;">8/17/2011</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FF0000;;">1/20/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #FF0000;;">2/8/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #FF0000;;">2/27/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #FF0000;;">3/18/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #FF0000;;">4/6/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #FF0000;;">4/25/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #FF0000;;">5/14/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #FF0000;;">6/2/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #FF0000;;">6/21/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">7/10/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">7/29/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">8/17/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">9/5/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">9/24/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;background-color: #FF0000;;">10/13/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;background-color: #FF0000;;">11/1/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;background-color: #FF0000;;">11/20/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;background-color: #FF0000;;">12/9/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;background-color: #FF0000;;">12/28/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">A2</font>)-1</font>)/3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=AND(<font color="Blue">A2<>"",(<font color="Red">(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">A2</font>)-1</font>)/3</font>)+1</font>)&YEAR(<font color="Green">A2</font>)<>(<font color="Green">INT(<font color="Purple">(<font color="Teal">MONTH(<font color="#FF00FF">$H$26</font>)-1</font>)/3</font>)+1</font>)&YEAR(<font color="Green">$H$26</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">D2</font>)-1</font>)/3</font>)+1</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Peter_SSs]

Another way:

Mark

Your C2 formula refers to $H$26, which you haven't shown us. Should that reference actually be $D$2?

I'm not sure why you have given formulas and shown us Columns B and E - the results don't seem to be used anywhere. Perhaps it was to show the development of the column C formula?

 

[markmzz]

Mark

Your C2 formula refers to $H$26, which you haven't shown us. Should that reference actually be $D$2?

I'm not sure why you have given formulas and shown us Columns B and E - the results don't seem to be used anywhere. Perhaps it was to show the development of the column C formula?

Peter,

Thanks for the note.

The cell H26 is one of my test cell, but, in this case, the correctly cell is D2.

The columns B and E was there only for reference (tests cells too).

Here is the correct formula and a little small:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;">Quart.</td><td style="font-weight: bold;text-align: center;background-color: #FF0000;;">CF</td><td style="font-weight: bold;text-align: center;;">Today is</td><td style="font-weight: bold;text-align: center;;">Quart.</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #FF0000;;">1/1/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;">8/17/2011</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #FF0000;;">1/20/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #FF0000;;">2/8/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #FF0000;;">2/27/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #FF0000;;">3/18/2011</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #FF0000;;">4/6/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #FF0000;;">4/25/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #FF0000;;">5/14/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #FF0000;;">6/2/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #FF0000;;">6/21/2011</td><td style="text-align: center;;">2</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">7/10/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">7/29/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">8/17/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">9/5/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">9/24/2011</td><td style="text-align: center;;">3</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;background-color: #FF0000;;">10/13/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;background-color: #FF0000;;">11/1/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;background-color: #FF0000;;">11/20/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;background-color: #FF0000;;">12/9/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;background-color: #FF0000;;">12/28/2011</td><td style="text-align: center;;">4</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td><td style="text-align: center;;">*************</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">A2</font>)-1</font>)/3</font>)+1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=AND(<font color="Blue">A2<>"",(<font color="Red">INT(<font color="Green">(<font color="Purple">MONTH(<font color="Teal">A2</font>)-1</font>)/3</font>)+1</font>)&YEAR(<font color="Red">A2</font>)<>(<font color="Red">INT(<font color="Green">(<font color="Purple">MONTH(<font color="Teal">$D$2</font>)-1</font>)/3</font>)+1</font>)&YEAR(<font color="Red">$D$2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">D2</font>)-1</font>)/3</font>)+1</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[guppyguy]

Actually, for what I am doing, the first solution was adequate.

Thanks again, as always, very impressed with the amount help every one offers here!

Gup

 

[ChrisOswald]

So, if I'm reading correctly, I had three problems in the conditional formatting formula I posted.

1) I missed subtracting 1 from the now() to keep stuff correct on the first month of each quarter.
I think that this is what Peter was talking about having some dates format incorrectly (any date that is month 1,4,7,10)

2) I didn't make sure that the dates were from the same year.

3) I didn't trap for blanks/non -dates.

If that's what I got wrong (and 3 mistakes in a three sentance problem statement is not a personal record), using the same formatting scheme, this is a correction:

=NOT(AND((INT((MONTH($A2)-1)/3)=INT((MONTH(NOW())-1)/3)),(YEAR($A2)=YEAR(NOW())),(ISNUMBER($A2))))

Peter; Somehow I missed using the Ceiling (and Floor) functions, instead always using int() type nestings. Thanks for pointing it out.

 

[Peter_SSs]

@Chris

1) I missed subtracting 1 from the now() to keep stuff correct on the first month of each quarter.

The subtracting 1 from from the NOW month was the issue, but it is to correct for the last month of each quarter, not the first.

For example, with your earlier formula, suppose the current month (NOW) was September and the month in A2 was August. They are in the same quarter but for August ..
INT((MONTH($A2)-1)/3)
=INT((8-1)/3)
=2

.. while for September ..
INT(MONTH(NOW())/3)
=INT(9/3)
=3

3) I didn't trap for blanks/non -dates ..
.. this is a correction:

=NOT(AND((INT((MONTH($A2)-1)/3)=INT((MONTH(NOW())-1)/3)),(YEAR($A2)=YEAR(NOW())),(ISNUMBER($A2))))

This still formats blanks. You have your NOT/AND/ISNUMBER arrangement a bit mixed up.

Peter; Somehow I missed using the Ceiling (and Floor) functions, instead always using int() type nestings. Thanks for pointing it out.

There's nothing wrong with the INT approach, CEILING is just an alternate one.


BTW, not a big issue but NOW() includes the date and time. Since the time is not relevant in determining the month, the TODAY() function, which just returns the date, would be sufficient.



@Gup
You say the first solution was adequate.
I again point out that although it works while we are still in August, it will fail when the current date turns to September in a week or so.