Averaging Times in Excel Across Dates

[Guest]

I've looked in all the threads here related to time calculations and have not found anything to help, besides all the time I've already spent trying to sort this out myself. Here's my situation:

I am trying to average the time from a whole column (e.g. J) of numbers that spread across the days of a month. It obviously worked okay averaging these when I was running daily numbers (same date). The cells in J are formatted as hh:mm:ss. These data in J are calculated using a formula that references other columns (C & D) on the same row. The formula for the J cells is;
=IF (or(ISBLANK(C693), ISBLANK(D693)), TIME(0,0,0), IF (D693>=C693, D693-C693, TIME(0,0,0))).
The formula works fine

The reference cells in the formula for columns C & D are formatted as (respectively); hh:mm (e.g. 15:52) and MM/DD/YYYY (e.g. 11/26/2003). However, the data in the cells for column D come from a referenced raw data worksheet. This data is a download from a database and is formatted in the raw data worksheet MM/DD/YYYY hh:mm (e.g. 11/26/2003 15:59).

I am sure I've probably overcomplicated the solution I'm trying to get, which is what I tend to do. Nonetheless, I have not been able to figure out how to either edit the formula for the data in column J or the average formula to only accept the net times and not account fo rthe dates.

From my daily data my average times run in the minutes. The result I'm getting from the average times in my monthly compilation (problem noted above) run in the hours. Which tells me (after thorough data cleansing) that it's calculating the entire date and net time not just the net time shown.

Thanks...

Steve A

 

[Alan]

I think this works assuming you dont need to go back to the twenties in your
list, try it
In A1
02 12 03 23:43
is
37957.98865
so
=LEFT(A1,5)
is 37957
and
=A1-LEFT(A1,5)
is
0.98865
which is
00 01 00 23:43
ie all the same date if copied down the list of various dates,
AVERAGE then works on the Time only ignoring the Date when formatted as
time,

Regards,
Alan.

I've looked in all the threads here related to time calculations and have

not found anything to help, besides all the time I've already spent trying
to sort this out myself. Here's my situation:

I am trying to average the time from a whole column (e.g. J) of numbers

that spread across the days of a month. It obviously worked okay averaging
these when I was running daily numbers (same date). The cells in J are
formatted as hh:mm:ss. These data in J are calculated using a formula that
references other columns (C & D) on the same row. The formula for the J
cells is;

=IF (or(ISBLANK(C693), ISBLANK(D693)), TIME(0,0,0), IF (D693>=C693, D693-C693, TIME(0,0,0))).
The formula works fine

The reference cells in the formula for columns C & D are formatted as

(respectively); hh:mm (e.g. 15:52) and MM/DD/YYYY (e.g. 11/26/2003).
However, the data in the cells for column D come from a referenced raw data
worksheet. This data is a download from a database and is formatted in the
raw data worksheet MM/DD/YYYY hh:mm (e.g. 11/26/2003 15:59).

I am sure I've probably overcomplicated the solution I'm trying to get,

which is what I tend to do. Nonetheless, I have not been able to figure out
how to either edit the formula for the data in column J or the average
formula to only accept the net times and not account fo rthe dates.

From my daily data my average times run in the minutes. The result I'm

getting from the average times in my monthly compilation (problem noted
above) run in the hours. Which tells me (after thorough data cleansing) that
it's calculating the entire date and net time not just the net time shown.