time question

[Guest]

i need to find the difference in two times - displayed as a decimal - eg 1700
- 1830 = 1.5

so far my formula is:

=IF(A2<>"",TEXT(sheet1!K2)-(sheet1!J2)"h:mm","")

where am i going wrong?

cheers

 

[Ardus Petus]

Try:
=(TIME(LEFT(K2,2),RIGHT(K2,2),0)-TIME(LEFT(J2,2),RIGHT(J2,2),0))*24

Cheers,

 

[RudeRam]

Here is one formula I use to get time duration:

=IF(OR(F2="",G2=""),"",(G2-F2+(G2<F2))*24)

Just change the cell references to your sheet

Ensure you have your cells as numbers or time not text

Rick

 

[Guest]

I've tried this and get -0.13

=IF(A2<>"",(((TIME(LEFT(sheet1!K2,2),RIGHT(sheet1!K2,2),0)-TIME(LEFT(sheet1!J2,2),RIGHT(sheet1!J2,2),0))*24)),""

 

[Dav]

I would agree with AP's response. if your original times are number or
text. If however they are times eg 18:00 just subtract one value from
the other, multiply by 24 and format as a number

Regards

Dav

 

[Dave Peterson]

I put 1830 in A2 and 1700 in A1:

And this formula in A3:
=(TEXT(A2,"00\:00")-TEXT(A1,"00\:00"))*24

And I formatted A3 as General.