Determining quarters from year, month & =now()

[lenik]

Hello all,

This is what I am trying to accomplish:

I need to create a filter/formula for Column H. If Column C is in the current quarter, I need H to display "Current Quarter". If Column C is in the last quarter, I need H to display "Last Quarter". For anything else previous to these two quarters, Column H can simply reflect the value of Column G.

How do I get these quarter formulas to calculate based on the current date?

Please let me know if further detail is needed. Any ideas or suggestions are greatly appreciated!

Excel Workbook
	A	B	C	D	E	F	G	H
1	Now	Month	Invoice Date	Invoice Month	Invoice Qtr	Year	Value	Label
2	2/9/2009	2	1/5/2009	1	1	2009	2009Q1	Current Quarter
3	2/9/2009	2	12/15/2008	12	4	2008	2008Q4	Last Quarter
4	2/9/2009	2	11/1/2008	11	4	2008	2008Q4	2008Q4
5	2/9/2009	2	9/22/2008	9	3	2008	2008Q3	2008Q3
6	2/9/2009	2	6/5/2008	6	2	2008	2008Q2	2008Q2
7	2/9/2009	2	2/14/2008	2	1	2008	2008Q1	2008Q1
Sheet

 

[jbeaucaire]

Again, a long convoluted "pulling apart" of the possible quarters and years, so I made a chart off to the right that will cover many years. Then this will analyze and return the values. Copy the two formula down their respective columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	L	M	N
1	Now	Month	Invoice Date	Invoice Month	Invoice Qtr	Year	Value	Label			2008Q1	2008Q1
2	2/9/2009	2	1/5/2009	1	1	2009	2009Q1	Current Quarter			2008Q2	2008Q2
3	2/9/2009	2	12/15/2008	12	4	2008	2008Q4	Last Quarter			2008Q3	2008Q3
4	2/9/2009	2	11/1/2008	11	4	2008	2008Q4	Last Quarter			2008Q4	Last Quarter
5	2/9/2009	2	9/22/2008	9	3	2008	2008Q3	2008Q3			2009Q1	Current Quarter
6	2/9/2009	2	6/5/2008	6	2	2008	2008Q2	2008Q2			2009Q2	2009Q2
7	2/9/2009	2	2/14/2008	2	1	2008	2008Q1	2008Q1			2009Q3	2009Q3
8											2009Q4	2009Q4
9											2010Q1	2010Q1
10											2010Q2	2010Q2
11											2010Q3	2010Q3
12											2010Q4	2010Q4
13											2011Q1	2011Q1
14											2011Q2	2011Q2
15											2011Q3	2011Q3
16											2011Q4	2011Q4
17											2012Q1	2012Q1
18											2012Q2	2012Q2
19											2012Q3	2012Q3
20											2012Q4
Sheet3

 

[barry houdini]

Perhaps try this formula in H2 copied down

=IF((YEAR(A2)=F2)*(INT((MONTH(A2)+2)/3)=E2),"Current Quarter",IF((YEAR(A2)-(MONTH(A2)<4)=F2)*(INT((MONTH(A2)-1)/3)=MOD(E2,4)),"Last Quarter",G2))

 

[Peter_SSs]

lenik

A few comments/suggestions.

1. When using Excel jeanie, not sure if you are aware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. You could have just shown us the row 2 formulas and told us they were copied down.

2. It looks like you are using =NOW() to return the current date. If that is the case, it makes more sense to use =TODAY() as =NOW() also includes the current time and I don't think that is required for your purpose here.

3. Both =NOW() and =TODAY() are volatile functions so best to avoid using them too much on a shet as they can cause your sheet to become slow. More on this below.

4. Do you really need the current date and month repeated down every row in columns A & B?

- If, so I suggest putting =TODAY() in a separate cell (I used K1) and refer to that cell in the column A formulas as I have done in the top section of my sheet below. This helps with the 'volatile function' issue mentioned above as =TODAY() only needs to be calculated once. I have then used a couple of extra helper cells in column K and made a suggestion for a new formula for column H. (Note that I get a different result to you for row 4, as did jbeaucaire. I am assuming you just made a mistake with your sample data there?)

- If not, and maybe you don't really need some of the other columns either, I have made a different suggestion in the bottom section of my sheet below.

All formulas that I have shown for columns A:H are copied down in their respective sections of the sheet (note the limited number of actual formulas shown below - refer point 1 above).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Now	Month	Invoice Date	Invoice Month	Invoice Qtr	Year	Value	Label		Today	10/02/2009
2	10/02/2009	2	05/01/2009	1	1	2009	2009Q1	Current Quarter		Current Qtr End	31/03/2009
3	10/02/2009	2	15/12/2008	12	4	2008	2008Q4	Last Quarter		Last Qtr End	31/12/2008
4	10/02/2009	2	01/11/2008	11	4	2008	2008Q4	Last Quarter
5	10/02/2009	2	22/09/2008	9	3	2008	2008Q3	2008Q3
6	10/02/2009	2	05/06/2008	6	2	2008	2008Q2	2008Q2
7	10/02/2009	2	14/02/2008	2	1	2008	2008Q1	2008Q1
8
9
10			Invoice Date	Invoice Qtr End			Value	Label		Today	10/02/2009
11			05/01/2009	31/03/2009			2009Q1	Current Quarter		Current Qtr End	31/03/2009
12			15/12/2008	31/12/2008			2008Q4	Last Quarter		Last Qtr End	31/12/2008
13			01/11/2008	31/12/2008			2008Q4	Last Quarter
14			22/09/2008	30/09/2008			2008Q3	2008Q3
15			05/06/2008	30/06/2008			2008Q2	2008Q2
16			14/02/2008	31/03/2008			2008Q1	2008Q1
17
Quarters