Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Any help you can provide is appreciated.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    New Lounger
    Join Date
    Nov 2003
    Location
    Texas, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •