Need data entered to reflect hh:mm format

[Peter]

I run average time it takes to do tasks. The formula for my end product is
=IF(I5="","",N5-I5+(N5<I5)). If the time in I5 is 23:40 and my time in N5 is
00:20 the cell in N5 shows the time as 0:40 but when I average my end product
it is wrong because it is showing the average for N5 as 24 hours I have to
change I5 11:40 and N5 to 12:20 to get my average correct. How do I get the
cell in N5 to read as 00:20 and my end product to read 0:40?

 

[Bernie Deitrick]

Peter,

Your problem statement is unclear because your explanation uses N5 for two
different things:

"my time in N5 is 00:20 the cell in N5 shows the time as 0:40"

What cell has your formula, and which cells are you averaging?

HTH,
Bernie
MS Excel MVP

 

[JBeaucaire]

I always convert time math back into standard values before
adding/subtracting/averaging.

For instance, your formula I would convert the answers into something
meaningful like:

MINUTES:
=IF(I5="","",N5-I5+(N5<I5))*1440

HOURS:
=IF(I5="","",N5-I5+(N5<I5))*24

Format these cells as GENERAL. It's simple to do math on these values and
get correct answers.

 

[JBeaucaire]

Oops, be sure to slip this multipliers inside the last paren:

=IF(I5="","",N5-I5+(N5<I5)*24)

Sorry about that.