Subtract Decimal time

[Steved]

Hello from Steved

A formula for below please

9.30am from 12.25pm leaves 2.55 hrs

Yes it is a decimal point

Thankyou.

 

[Bob Phillips]

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

--

HTH

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

 

[Steved]

Hello from Steved

Using the formula you have given me leaves -70.88

 

[Ron Rosenfeld]

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

 

[Steved]

Thanks very much

-----Original Message-----



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
.

 

[Steved]

Hello Ron From Steved

Please is it possible using your formula to get

12.20 from 3.17 will leave 2.57

thankyou.

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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*(A1>A2).

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*(A1>A2),60)-dollarde(A1,60),60)

or

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


--ron

 

[Steved]

Thankyou Ron for taking time to explain.

Cheers

-----Original Message-----


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*(A1>A2).

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*(A1>A2),60)-dollarde(A1,60),60)

or

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


--ron
.

 

[Ron Rosenfeld]

Thankyou Ron for taking time to explain.

Cheers

You're welcome. I appreciate the feedback.

--ron