Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting the number of days in a range of dates (2000/2003)

    I need help with a formula that will count the number of days in a range of dates by month. The column heading on the data worksheet is Date Issue Opened (The spreadsheet tracks issues reported to our HR department) The column is simply formatted as dates in the MM/DD/YY format. I need to create a report on a separate worksheet that references the dates and gives a simple count of the number of issues reported in a particular month. Each row on the report page will be a month:
    .........A.............................B
    1..MONTH: Number of Isues Reported
    2..Jan.........................12
    3..Feb.........................21
    4..Mar.........................14
    ETC

    So what I need is a formula for each month that will reference the entire column of dates on the data worksheet that will pull out only the days in the associated month, i.e. there are14 issues reported in January, 21 reported in Feb, 12 reported in Mar, etc. I can modify the formula on the report page for each month and of course can change the date formatting on the data worksheet as well to make the counting of the dates by month easier if that is necessary. I have tried COUNT and COUNTIF but I can't figure out how to set up the criteria so it only counts/filters a specific month.

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

    Re: Counting the number of days in a range of dates (2000/2003)

    Edited by HansV to correct typo (thanks to <!profile=Rudi>Rudi<!/profile> for pointing it out!)

    The easiest way to do this is a pivot table. If you create a pivot table, with the date as row field and also as data field (with function COUNT), you'll get a pivot table grouped by date. Click on one of the dates or on the gray row field button, then select Pivot Table | Group and Outline | Group... from the Pivot Table toolbar. Click the Months option, then click OK.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting the number of days in a range of dates (2000/2003)

    OK, this is starting to work. It took me a while and some reformatting of the date column. I had to add another column that display only the month and year so that it would come up with a total number of issues for each month.

    Now, they want to include data from multiple worksheets. I am trying to find out how to include multiple worksheets in a pivot table, but if you have any knowledge on how to do this that would be great.

    Thanks again for the help.

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

    Re: Counting the number of days in a range of dates (2000/2003)

    The first step of the Pivot Table Wizard has an option to create a pivot table from multiple consolidation ranges. I don't have much experience with this option; you will find some info in Microsoft Office Assistance: PivotTable reports 101.

Posting Permissions

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