Share via

Average Time spanning midnight

Anonymous
2011-03-02T05:29:55+00:00

Is there a way to calculate average times spanning midnight without entering the date?

All the times I use are between 9 PM and 9 AM

If I entered 11 PM and 3 AM, I would want the average time to be 1 AM.

If I entered 9 PM and 3 AM, I would want the average time to be 12 AM.

Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-03T05:05:01+00:00

    I'm sorry..I should have been more specific. I only used two times in my examples. What if I had to find the average of 50-100 times? Thanks.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-03-02T07:36:27+00:00

    =A2+MOD(B2-A2,1)/2

    Regards,

    Bernd

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2011-03-02T06:49:31+00:00

    Let's say start time is in A2 and end time in B2.

    Use this formula:

    =(A2+B2+(A2>B2))/2

    and format the result as time.

    Was this answer helpful?

    0 comments