time question

[sedonovan]

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,
--
AP

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

[sedonovan]

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" <[email protected]> a écrit dans le message de
> news: [email protected]...
> >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
>
>
>

[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.

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