Average of Times Spanning Midnight

Hi there,

I have a simple formula which calculates a team's overall average (using their daily time finished)
=AVERAGE(D8:AA8)

However, one of the times from a certain day goes past midnight (Ex. 1:00AM) and this number improves the average and makes it seem earlier, when it should be in fact later.

How do I solve this?

Thanks!

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Hi,

How should the system determine whether the time in question, e.g. 01:47 is indeed the next day and not the current day. Is there some starting time which could be used to determine whether atime before that means the next day? For instance the earliest time excluding 01:47 is 12:28. Does this imply there could be a rule that says a time less than say 12:00 means the following day?

Would you add examples of the results you expect to see on a few of the rows given the 6:00 PM rule you suggest so that we can see the end goal.

What is the range of times that this can fall into each day? Say, for example, that the times for a given day fall between 7:00 PM and 2:00 AM the next morning. (19:00 to 02:00). I would add 12 hours to times earlier than noon, and take the average of that.

This can be done with an array formula. Enter this formula into the cell

Formula:

=AVERAGE(IF(D8:AA8<0.5,D8:AA8+0.5,D8:AA8))

then instead of hitting ENTER, hit the combination CTRL-SHIFT-ENTER. If you have done it correctly you will see brackets around the formula in the formula bar.

Formula:

{=AVERAGE(IF(D8:AA8<0.5,D8:AA8+0.5,D8:AA8))}

You cannot do this by typing the brackets; you must hit the combination CTRL-SHIFT-ENTER.

(This assumes that times after midnight are rare. If by some quirk your average is later than midnight, then the result would be 12 hours later.)

Originally Posted by Richard Buttrey

Would you add examples of the results you expect to see on a few of the rows given the 6:00 PM rule you suggest so that we can see the end goal.

I would just like for Row 27 and Column AB to correctly display the average time for the team or day, such as "9:00 PM".

Originally Posted by 6StringJazzer

What is the range of times that this can fall into each day? Say, for example, that the times for a given day fall between 7:00 PM and 2:00 AM the next morning. (19:00 to 02:00). I would add 12 hours to times earlier than noon, and take the average of that.

This can be done with an array formula. Enter this formula into the cell

Formula:

=AVERAGE(IF(D8:AA8<0.5,D8:AA8+0.5,D8:AA8))

then instead of hitting ENTER, hit the combination CTRL-SHIFT-ENTER. If you have done it correctly you will see brackets around the formula in the formula bar.

Formula:

{=AVERAGE(IF(D8:AA8<0.5,D8:AA8+0.5,D8:AA8))}

You cannot do this by typing the brackets; you must hit the combination CTRL-SHIFT-ENTER.

(This assumes that times after midnight are rare. If by some quirk your average is later than midnight, then the result would be 12 hours later.)

Okay yeah like average times should be 6PM to 2AM. Why add 12 hours? Shouldn't it be 24?

Also this formula is picking up on the blanks now, I tried:
=AVERAGE(IF(AND(D7:AA7<0.5,D7:AA7<>0),D7:AA7+1,D7:AA7))
But it appears not to be working :S

Originally Posted by LJenny

I would just like for Row 27 and Column AB to correctly display the average time for the team or day, such as "9:00 PM".

The reason I asked you to manually calculate and enter in the workbook the average you expect to see for a few example rows is that experience tells us that this whole process of answering requests is much helped if we know the end goal. It save a lot of to-ing and fro-ing and misuderstanding. We can test our ideas knowing what the result should be.

And incidentally there's no need to requote previous comments unless absolutely necessary for clarification.