Average Time Across Midnight

I'm looking for a better way to allow my pivot table to display the correct average time when the range of values contain times both before and after midnight.

Incorrect Average

11:56 PM
12:17 AM
12:05 AM
8:06 AM Average

Correct Average
11:56 PM
12:17 AM
12:05 AM
12:06 AM Average

Currently, my solution has been to locate all of the post-midnight times and add 24:00 hours to them (12:17 AM >>> 24:17; 1:37 AM >>> 25:37). This works, but my source data is dynamic so having to go in and change it before updating the tables seems prone to problems.

Any ideas on how to workaround this?

Maybe this:

Formula:

Please Login or Register  to view this content.

Comfirmed with ctrl+shift+enter (and not just enter).

But I would need more examples if this is not good solution.

Only next time, when you uploading example, please upload example workbook (not picture). nobody want to retype your data from the picture.

Originally Posted by zbor

Only next time, when you uploading example, please upload example workbook (not picture). nobody want to retype your data from the picture.

Yeah, sorry about the attachment - I could not figure out how to remove it prior to posting.

I think you formula would work, if not for the fact that the Pivot Table is doing the calculation. I'll keep trying things.

Thanks for the quick response.