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 a
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) a-
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 a
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

So how are you defining your "average"?
What is the "average" of 11:00 and 23:00?
What is the "average" of 12:00 and 23:00?
What is the "average" of 10:00 and 23:00?

Tell us what algorithm you want to use, and we can then hopefully show you
how to do it in Excel.
Before what time do you want a time to be treated as if it were the end of
the previous day rather than the start of that day?
--
David Biddulph

"WOLLAM" wrote in message
...
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 a
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) a-
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 a
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