Thank you in advance for all the help.
I have read the recent posts about averaging time, but none fit my question.
I am trying to average a set of times that are on both sides of 12midnight.
All of the formulas I have been attempting are averaging the times as if they
are times of day that are several hours apart; when in fact they are just a
couple of hours apart. The times for my example are:
12:45 am
11:01 pm
2:05 am
The average of these times should be 12:37 am. 'Simple' average formula
results in 8:37 am. I have toyed with some of the other formulas mentioned
in the others posts, but to no avail. Thanks again for all the help.
Dan

Times are actually fractions of a whole day. 12:00 midday is 0.5 days.

The actual numeric representations of your times (4 dec places only) are:-
12:45 am = 0.0313
11:01 pm = 0.9590
2:05 am = 0.0868

Therefore it sees your 12:45 am as just after midnight but on the same day
as 11:01 pm. That is it is in the morning of the same day.

If 12:45am and 2:05 am is actually after 11:01pm then it is the next day and
must have one added to them and if formatted as numeric to 4 deciamal places
they would be
12:45 am = 1.0313
11:01 pm = 0.9590
2:05 am = 1.0868

If you then average those values and format the answer to hours and minutes
then you will get 12:37

Work on it form there and get back to me if still having a problem. My
first suggestion is that you insert dates and times together in the one cell.
Your final answer can be formatted as just hh:mm or [hh]:mm if you want hours
greater than 24 to display as hours and not roll over to the next day's time.

Aso you can add 1 to a time in a cell formatted as time.

Hope I have not confused you too much.


--
Regards,

OssieMac


"WOLLAM" wrote:

> Thank you in advance for all the help.
> I have read the recent posts about averaging time, but none fit my question.
> I am trying to average a set of times that are on both sides of 12midnight.
> All of the formulas I have been attempting are averaging the times as if they
> are times of day that are several hours apart; when in fact they are just a
> couple of hours apart. The times for my example are:
> 12:45 am
> 11:01 pm
> 2:05 am
> The average of these times should be 12:37 am. 'Simple' average formula
> results in 8:37 am. I have toyed with some of the other formulas mentioned
> in the others posts, but to no avail. Thanks again for all the help.
> Dan

OssieMac,
Thanks so much. With your suggestion and a helper column I was able to egt
the desired result. Thanks again.
Dan

"OssieMac" wrote:

> Times are actually fractions of a whole day. 12:00 midday is 0.5 days.
>
> The actual numeric representations of your times (4 dec places only) are:-
> 12:45 am = 0.0313
> 11:01 pm = 0.9590
> 2:05 am = 0.0868
>
> Therefore it sees your 12:45 am as just after midnight but on the same day
> as 11:01 pm. That is it is in the morning of the same day.
>
> If 12:45am and 2:05 am is actually after 11:01pm then it is the next day and
> must have one added to them and if formatted as numeric to 4 deciamal places
> they would be
> 12:45 am = 1.0313
> 11:01 pm = 0.9590
> 2:05 am = 1.0868
>
> If you then average those values and format the answer to hours and minutes
> then you will get 12:37
>
> Work on it form there and get back to me if still having a problem. My
> first suggestion is that you insert dates and times together in the one cell.
> Your final answer can be formatted as just hh:mm or [hh]:mm if you want hours
> greater than 24 to display as hours and not roll over to the next day's time.
>
> Aso you can add 1 to a time in a cell formatted as time.
>
> Hope I have not confused you too much.
>
>
> --
> Regards,
>
> OssieMac
>
>
> "WOLLAM" wrote:
>
> > Thank you in advance for all the help.
> > I have read the recent posts about averaging time, but none fit my question.
> > I am trying to average a set of times that are on both sides of 12midnight.
> > All of the formulas I have been attempting are averaging the times as if they
> > are times of day that are several hours apart; when in fact they are just a
> > couple of hours apart. The times for my example are:
> > 12:45 am
> > 11:01 pm
> > 2:05 am
> > The average of these times should be 12:37 am. 'Simple' average formula
> > results in 8:37 am. I have toyed with some of the other formulas mentioned
> > in the others posts, but to no avail. Thanks again for all the help.
> > Dan