Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Location
    Charleston, South Carolina, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average Time Function (2003)

    Can someone please give me a hand with a calculation I have to do in Excel. I have two columns with dates and time in each Cell. The format of the Date Time Group is 3/6/2008 9:27. I need to calculate the difference in time between column A and Column B and place it in column C. Then I need the average time of all the time in Colum C.
    Attached is an example of what I'm working with

    Thank you in advance.
    Attached Files Attached Files

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

    Re: Average Time Function (2003)

    You can enter the following formula in C2:

    =B2-A2

    and fill down. You'll see that there is a problem in row 3, since the end time is before the start time; this will cause C3 to show an error value.

    You can use =AVERAGE(C2:C17) to calculate the average difference.
    If you'd prefer to avoid the individual difference formulas, you can use =AVERAGE(B2:B17)-AVERAGE(A2:A17)

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Location
    Charleston, South Carolina, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average Time Function (2003)

    Thank you Hans.
    I was thinking it was easier than I was making it. I had one of the columns formatted as text by accident.

    On a related note. Do you know how I can use the average of the times but ignore 0:00 values?
    For example the average of Rows C10 - C14 is 0:03 right now. But If I ignore the 0:00 Value in C10 then the average is 0:04.

    Sean

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

    Re: Average Time Function (2003)

    I'll assume that you have entered the difference formulas in C2:C17.
    To calculate the average of the non-zero differences, you can use the following array formula (confirm with Ctrl+Shift+Enter instead of just Enter):

    =AVERAGE(IF(C2:C17>0,C2:C17))

Posting Permissions

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