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

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

Cheers,
--
AP

"sedonovan" a écrit dans le message de
news: ...
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

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


--
RudeRam


------------------------------------------------------------------------
RudeRam's Profile: http://www.excelforum.com/member.php...fo&userid=8818
View this thread: http://www.excelforum.com/showthread...hreadid=555600

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)) ,""




"Ardus Petus" wrote:

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

Cheers,
--
AP

"sedonovan" a écrit dans le message de
news: ...
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

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=555599

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.

sedonovan wrote:

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

--

Dave Peterson