Sequence year and quarter

[agibsonsw]

Hello. Excel 2007.
I'm trying to create a two-column sequence that runs
2010 Qtr1
2010 Qtr2
2010 Qtr3
2010 Qtr4
2011 Qtr1, etc.
How can I do this using AutoFill or perhaps a formula? Thanks, Andy.

[cshenoy]

I'd put the first 4 year and qtr values in columns A and B. In the 6th row, if you have titles in row 1, the Column A formula in A6 would be A2 +1, B6 would have =B2.Copy those as far as you need.

That said, I frequently need the actual dates and want to convert them to the quarter format. You can accomplish this with formatting in the date column and vlookup formula in the quarter column. The attached spreadsheet shows both ways.

Put your first date in D2, then use the eomonth formula =EOMONTH(D2,3). Use a custom format YYYY to show just the year. In E2 and subsequently, =VLOOKUP(MONTH(D2),lookuptable,2).

The lookuptable would be:
0 Q1
4 Q2
7 Q3
10 Q4

[HansV]

Another option:

In B1, enter Qtr1.
Drag the fill handle in the lower right corner of B1 down to fill as far as needed. Excel is smart enough to start at Qtr1 again after Qtr4.
In A1, enter the starting year 2010.
In A2, enter the formula =A1+(B2="Qtr1")
Drag the fill handle in the lower right corner of A2 down to fill as far as needed.

[agibsonsw]

Thank you very much. Andy.