Diffrence Between two hours

[Sushanth]

Hie,
Can anyone help me in finding the diffrence between two hours.ie if if work at 11 am till 9.25 PM , difference between them.
I have tried many formula but it showing error as value

 

[bobhc]

Good day Sushanth

Start time in A1 End time in B1 put in to C1 =B1-A1


You will need to format C column to get rid of AM

 

[Xiq]

If you multiply the result bobhc gave you with 24, you will have the difference in decimal.

=(B1-A1)*24

 

[NARAYANK991]

Hi Sushanth ,

Please remember that times in Excel are denoted by the colon ":" ; thus 9.25 pm is not a valid time , 9:25 pm is.

Thus if you enter 9.25 pm in one cell , say A1 , and 11 am in another , say B1 , then =A1-B1 will give an error value #VALUE! ; if you change the data in A1 to 9:25 pm , then the formula will give 10:25 as the result.

Narayan

 

[Sushanth]

Thanks a lot it worked

 

[Pete]

If you are going to have result answers that are greater that 24 hours in duration, change your custom formatting to [h]:mm to see the correct result. Otherwise your answer will roll to the next day. For example if there is a 25 hour difference in your times you will see 1:00 instead of the correct 25:00.

 

[SirJB7]

Hi, Sushanth!

My two cents.

If subtracting 2 date/time formatted cell values this happens:
C1: =A1-B1
a) if target cell (C1) is formatted as date/time
if the subtrahend (B1) is greater than the minuend (A1), error value is returned
if the subtrahend is less or equal than the minuend, difference (positive) is returned
b) if target cell is formatted as general (or number, but not as date/time)
if the subtrahend is greater than the minuend, difference (negative) is returned
if the subtrahend is less or equal than the minuend, difference (positive) is returned

You can avoid the error value in two ways:
1) quick & dirty
=ABS(A1-B1)
and then you'll have to control/decide how to treat the result in other calculations
2) long
=IF(A1>=B1,A1-B1,B1-A1)
and same as above.

Hope it helps.

Regards!