How to calculate time difference for time beyond 12AM – Excel

Calculate time difference in excel is easy but real problem comes up when second time is more than 12 AM. The solution for it is either you mention time with date or use a suitable formula which increments day for you.

What is the problem

When two times is subtracted using formula
Second time – first time you get result as below

Time difference in Excel

But the moment time goes beyond 12 AM result is

Time difference in Excel

This is because your time does not have any date. Here excel uses its default date i.e. 00-01-1900 ( 0 January 1900). So the second time is now smaller and gives a negative result.

How to calculate time difference for time beyond 12AM

Use below formula

=IF(A2>B2,((B2+1)-A2),(B2-A2))

Formula increments date whenever timing is beyond 12 AM. Now the result as desiredtime difference 03

The above result is in format hh:mm. if you want to convert this time to minutes then multiply result 1440.

Or you can re-frame this formula as

=IF(A2>B2,(((B2+1)-A2)*1440),((B2-A2)*1440))

The result now derived will be minutes.

This formula is tested on Excel 2010.

Other articles which will interest you
How to calculate difference between two times in MS Excel