Subtract Decimal time
 

Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.

=INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steved" wrote in message
...
Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.

Hello from Steved

Using the formula you have given me leaves -70.88



-----Original Message-----
=INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steved" wrote in
message
...
Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.


.

On Tue, 30 Nov 2004 16:16:01 -0800, "Steved"
wrote:

Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.


If you don't mind installing the Analysis Tool Pak,

=dollarfr(dollarde(A2,60)-dollarde(A1,60),60)

or, more complicated, but without the ATP:

=INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+
MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1)*60/100

Later time is in A2, earlier time in A1


--ron

Thanks very much

-----Original Message-----
On Tue, 30 Nov 2004 16:16:01 -0800, "Steved"
wrote:

Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.


If you don't mind installing the Analysis Tool Pak,

=dollarfr(dollarde(A2,60)-dollarde(A1,60),60)

or, more complicated, but without the ATP:

=INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+
MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1)*60/100

Later time is in A2, earlier time in A1


--ron
.

Hello Ron From Steved

Please is it possible using your formula to get

12.20 from 3.17 will leave 2.57

thankyou.



-----Original Message-----
On Tue, 30 Nov 2004 16:16:01 -0800, "Steved"
wrote:

Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.


If you don't mind installing the Analysis Tool Pak,

=dollarfr(dollarde(A2,60)-dollarde(A1,60),60)

or, more complicated, but without the ATP:

=INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+
MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1)
*60/100

Later time is in A2, earlier time in A1


--ron
.

Hi SteveD,

Sorry, I read A1 and B1 as times, but I see these are decimal as well. This
will work as long as you enter miltary time (15.17)

=INT((A1-B1))+MOD((TIME(INT(A1),MOD(A1,1)*100,0)-TIME(INT(B1),MOD(B1,1)*100,
0)*1)*24,1)*60/100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steved" wrote in message
...
Hello from Steved

Using the formula you have given me leaves -70.88



-----Original Message-----
=INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steved" wrote in
message
...
Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.


.

On Tue, 30 Nov 2004 21:39:51 -0800, "Steved"
wrote:

Hello Ron From Steved

Please is it possible using your formula to get

12.20 from 3.17 will leave 2.57

thankyou.

Yes, it is. If you make the assumption that A2 is ALWAYS going to represent a
later time than A1, then you can modify the formulas by substituting for A2 the
expression: A2+12*(A1A2).

However, it might be simpler to use a 24 hr clock; such as 15.17 in your
example. This would then allow you to span times across midnight.

In any event, to answer your question, and assuming the two times represent
times that are on the same day:

=dollarfr(dollarde(A2+12*(A1A2),60)-dollarde(A1,60),60)

or

=INT(INT(A2+12*(A1A2))-INT(A1)+(MOD(A2+12*(A1A2),1)-
MOD(A1,1))*100/60)+MOD(INT(A2+12*(A1A2))-INT(A1)+
(MOD(A2+12*(A1A2),1)-MOD(A1,1))*100/60,1)*60/100


--ron

Thankyou Ron for taking time to explain.

Cheers

-----Original Message-----
On Tue, 30 Nov 2004 21:39:51 -0800, "Steved"
wrote:

Hello Ron From Steved

Please is it possible using your formula to get

12.20 from 3.17 will leave 2.57

thankyou.

Yes, it is. If you make the assumption that A2 is ALWAYS
going to represent a
later time than A1, then you can modify the formulas by
substituting for A2 the
expression: A2+12*(A1A2).

However, it might be simpler to use a 24 hr clock; such
as 15.17 in your
example. This would then allow you to span times across
midnight.

In any event, to answer your question, and assuming the
two times represent
times that are on the same day:

=dollarfr(dollarde(A2+12*(A1A2),60)-dollarde(A1,60),60)

or

=INT(INT(A2+12*(A1A2))-INT(A1)+(MOD(A2+12*(A1A2),1)-
MOD(A1,1))*100/60)+MOD(INT(A2+12*(A1A2))-INT(A1)+
(MOD(A2+12*(A1A2),1)-MOD(A1,1))*100/60,1)*60/100


--ron
.

On Wed, 1 Dec 2004 10:15:11 -0800, "Steved"
wrote:

Thankyou Ron for taking time to explain.

Cheers

You're welcome. I appreciate the feedback.

--ron