# Thread: count by quarter based on start/end dates (2003 SP1)

1. ## count by quarter based on start/end dates (2003 SP1)

Column A is Start Date, column B is End Date and columns C through whatever are labeled 1Q07, 2Q07, 3Q07, 4Q07 1Q08, etc. I'd like to get a "1" in the respective quarter/year columns based on the start and end dates. For example, a start date of 1/1/2007 and an end date of 5/15/07 would result in a "1" in columns 1Q07 and 2Q07 and so on.

Thanks

2. ## Re: count by quarter based on start/end dates (2003 SP1)

See attached workbook. The formulas can be filled down and right.
You can hide rows 2 and 3 if desired.

3. ## Re: count by quarter based on start/end dates (2003 SP1)

Wow - that was fast . I've got to learn that If/And thing. Thanks!

4. ## Re: count by quarter based on start/end dates (2003 SP1)

I now need to take this count by quarter info and determine in which quater the record begins. So my header row is across the top as 1Q08, 2Q08, etc, through a set range of 24 quarters. I'd like to add a column with a formula that will, for each row, look to the right and find the first cell containing a "1" and then give me the header name (i.e. "3Q08") of that column as the formula result.

Thanks

5. ## Re: count by quarter based on start/end dates (2003 SP1)

In the sample workbook I attached earlier, you can enter this formula in K4:

=INDEX(\$C\$1:\$J\$1,MATCH(1,C4:J4,0))

and fill down. The MATCH formula returns the index of the first cell where 1 is found in the row, and this is taken to look up the name of the quarter in row 1. See attached version.

6. ## Re: count by quarter based on start/end dates (2003 SP1)

Perfect. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•