While calculating difference between two time values in Excel, some cells display ######## instead of the expected result. Please advise on the cause of this issue and suggest a solution? Thank you.

Question

While calculating the difference in minutes between two-time values (Start Time and End Time) in Excel, some cells display ######## instead of the expected result. Could someone advise on the cause of this issue and suggest a solution? Thank you.

Answer 1

In the cells in your screenshot, the time in column I is before the time in column F, so the difference is negative.

Excel cannot display negative time.

Of course, the reason for this is that F is before midnight and I is after midnight.

The simplest solution is to use the date+time columns:

=G134-C134

If you prefer to keep on using columns F and I:

=MOD(I134-F134, 1)

(If Excel displays the result as a number, simply format the cells with the column as time)

Answer 2

Hi there...

Thanks for the reply. You are right.