I need to get this answer 8 - 1.2 = 6.2 from a formula

[T]

where 8 is hours 1.2 = one hour 1/2 and 6.2 = six and a half

need the same for the following on the same spreadsheet
1.1 = 1.25 and 1.3 = 1.75

 

[Bernie Deitrick]

T,

With the 8 in cell A1, and the 1.2 in cell B1, the formula (watch it, 'cause
it'll be line wrapped)

=INT((INT(A1)+(A1-INT(A1))*2.5)-(INT(B1)+(B1-INT(B1))*2.5))+((INT(A1)+(A1-IN
T(A1))*2.5)-(INT(B1)+(B1-INT(B1))*2.5)-INT((INT(A1)+(A1-INT(A1))*2.5)-(INT(B
1)+(B1-INT(B1))*2.5)))/2.5

will return 6.2, and so on for all your examples.

HTH,
Bernie
MS Excel MVP

 

[T]

That works!!! It took me a minute to figure out I needed to get rid of the
page breaks -- but man am I going to look good.... Thank you

 

[Tim C]

One way:
=A1-B1-0.6*((MOD(B1,1)-MOD(A1,1))>0.01)

Tim C

 

[Bob Phillips]

.... but of course, you will tell everyone that is was Bernie who solved it?

Bob

 

[Bernie Deitrick]

Actually, I prefer Tim C's solution, so let's keep my name out of this, OK?
;-)

Bernie

 

[Tim C]

Isn't there or didn't there used to be a function that gives you the decimal
portion of a number (the equivalent of MOD(x,1))?

Or am I having flashbacks to old programming languages?

MOD doesn't do a very clean job. I wish I could to something like:

=A1-B1-0.6*(DEC(B1)>DEC(A1))

Tim C

 

[RagDyer]

For decimals, are you perhaps talking about:
=A1-INT(A1)
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Isn't there or didn't there used to be a function that gives you the decimal
portion of a number (the equivalent of MOD(x,1))?

Or am I having flashbacks to old programming languages?

MOD doesn't do a very clean job. I wish I could to something like:

=A1-B1-0.6*(DEC(B1)>DEC(A1))

Tim C

 

[hrlngrv]

Bernie Deitrick wrote...

With the 8 in cell A1, and the 1.2 in cell B1, the formula (watch it, 'cause
it'll be line wrapped)

=INT((INT(A1)+(A1-INT(A1))*2.5)-(INT(B1)+(B1-INT(B1))*2.5))
+((INT(A1)+(A1-INT(A1))*2.5)-(INT(B1)+(B1-INT(B1))*2.5)
-INT((INT(A1)+(A1-INT(A1))*2.5)-(INT(B1)+(B1-INT(B1))*2.5)))/2.5

will return 6.2, and so on for all your examples.

....

Can give noncompliant results when A1 < B1. Also could be shortened.

=SIGN(A1-B1)*(INT(MAX(A1,B1)-MIN(A1,B1))
+MOD(2.5*(MOD(MAX(A1,B1),1)-MOD(MIN(A1,B1),1)),1)/2.5)

 

[Tim C]

I'm sure I could come up with a dozen different formulas to do it. But I
distinctly remember a function that did it. But it may not have been in
Excel.

Tim C

 

[T]

INT(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5))+(MOD(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5),1)*0.4)

We've gotten this far -- now --can someone have this read negative numbers
as well?

 

[T]

thanks

INT(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5))+(MOD(INT(A1)+MOD(A1,1)*2.5+INT(B1)+MOD(B1,1)*2.5-(INT(C1)+MOD(C1,1)*2.5),1)*0.4)

We've gotten this far -- now --can someone have this read negative numbers
as well?