Can't get the difference in time correct

Are you sure the values in A1 & A2 are < 24 hours ?

If you apply a format to A1 & A2 of [hh]:mm what is displayed ?

[ ] displays in cumulative terms

(eg [mm]:ss would show the total in cumulative minutes and seconds, [ss] just seconds etc...)

It follows you should apply the same format to your AVERAGE calculation result...

Time in XL is Decimal - eg 6am = 0.25, noon = 0.5, 6pm = 0.75... it follows that 24 hours = 1.

Consider:

A1: 18:00
A2: 12:00
A3: =SUM(A1:A2)

In Time terms we know the sum is 30 hours so in terms of underlying numerics we know this equates to 1.25 (1 and 1/4 days) - we can see this if we format A3 as General.

If we format A3 as Time XL will display the Time portion of that value - and we know that is the Decimal part, ie 0.25 so we will see 06:00 (0.25)

If we apply a Custom Format of [hh]:mm to A3 we are telling XL to show us the entire value in terms of Time and not just the decimal portion ... as a result we will get 30:00 (1.25)

If we apply a format of [mm]:ss to A3 we are telling XL to do the same but now show us the entire value in terms of minutes (and seconds remainder) - eg 1800:00 (1800 minutes being 1.25 * 24 * 60)


On an aside please do not quote prior posts in their entirety - either use Post Reply button or quote only those parts necessary to maintain a logical flow to the thread