Average Elapsed Time

Excel 2013. All cells in this spreadsheet are formatted as Time.

Column E is the Start Time.
Column F includes minutes to deduct from the Elapsed time.
Column G contains no data.
Column H includes minutes to deduct from the Elapsed time.
Column I is the End Time.

Column J contains cells with this formula to calculate the Elapsed Time of the values in the row:
For example: =IF(I16=E16,I16-E16-F16-H16,I16-F16-H16+1-E16)

E F G H I J
16 6:00 AM 0:15 0:15 9:00 AM 2:30
17 11:45 PM 0:30 1:30 7:00 AM 5:15
18 11:00 PM 0:15 0:15 9:00 AM 9:30

How can I calculate the average of the elapsed times in Column J?

so, first, I'm assuming your J calc should really read

=IF(I16>=E16,...)

else it doesn't really make sense - i.e. your first result might be format as Time and thus show 2:30 but, in reality, it's actually storing 26:30 (1 day + 2:30) -- thereby distorting your Average.

personally, I would change / simplify your approach in J to below:

Formula:

J16: =MOD(I16-E16,1)-SUM(F16,H16)

format as time

copied down

to average, simply use =AVERAGE(Jx:Jy) where x & y represent your first / law row of times, again, format as time

Thanks very much XLent. Your calculation works like a charm.

One question though:

The MOD function is used to find a remainder after a number is divided by another number.

How are you using the MOD function and what is the ",1" in your use of the MOD function?

Thanks MrShorty!

more specifically, here, the MOD approach is useful for handling scenarios where you have only Time values (as opposed to DateTime) and where said times may cross midnight

the main advantage is that the MOD(end-start,1) will always return the correct answer without need for manipulation of source values, e.g:

where end > start

=MOD("12:00"-"06:00",1) --> MOD( 0.25 , 1) --> 0.25

where end < start

=MOD("22:00"-"04:00",1) --> MOD( -0.75 , 1) --> 0.25

so, this approach is the more succinct alternative to the traditional approach of:

=if(end<start,end+1,end)-start

HTH

Thank you XLent.

I have been using spreadsheets since Lotus 1-2-3. I actually programmed (macros) a large budget once and later ported it to Excel. How have you learned spreadsheets? Are you hired for spreadsheet work?

Thanks again,
Wayne