Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aggregating Time data (Excel 97)

    I'm trying to graph some audit trail data from a web based Intranet application to show user volumes across the day. I have several thousand data records for the day that are time stamped [the format is 06:47:15] and sorted in time order. I thought the easiest way would be to do a COUNTIF formula but I haven't been able to get the formula result to show anything but "0". The formula I have used is [=COUNTIF('audit file - 30-01-2002'!F2:F1000,">A2")] where the cell A2 contains a time in the format [06:00:00] and which is incremented in 10 minute intervals down the column. Once I can get a count I can subtract cells to get the number of items in that 10 minute period.

    Is there something wrong with my logic here? Is there a better way to approach the data than what I have tried? I looked at pivot tables but couldn't work out how to get the number of items in 10 minute increments for the day.

    Any assistance is appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Time data (Excel 97)

    One thing that springs to mind is that the cells *show* a time, but there may be a date component there that is hidden because of the formatting. Try formatting both your A2 and the time range as general. Check if one or both of them is larger than 1.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Time data (Excel 97)

    There are a lot of different things that could be causing your problem. Could you possibly attache an example of the workbook?
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Aggregating Time data (Excel 97)

    As Legare notes there could be many causes, but if cell A2 contains the reference value (the value which you are looking for numbers greater than), you have a syntax error:

    =COUNTIF('audit file - 30-01-2002'!F2:F1000,">A2")

    should probably be:

    =COUNTIF('audit file - 30-01-2002'!F2:F1000,">"&A2)

    As written your formula was looking for anything greater than the literal text "A2".
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Time data (Excel 97)

    Thankyou John [and also everyone else as well]. The problem WAS a syntax error. Placing the greater than symbol inside the quotes as you suggested corrected the error and the results are now exactly what I need.

    Muchly appreciated.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Time data (Excel 97)

    Good eye John!
    Legare Coleman

Posting Permissions

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