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

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

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" wrote in message
...
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

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
"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

Tim C

"T" wrote in message
...
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

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

Bob

"T" wrote in message
...

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
"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

Bernie

"Bob Phillips" wrote in message
...
... but of course, you will tell everyone that is was Bernie who solved
it?

Bob

"T" wrote in message
...

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

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


"Tim C" wrote in message
...
One way:
=A1-B1-0.6*((MOD(B1,1)-MOD(A1,1))0.01)

Tim C

"T" wrote in message
...
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

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


Regards,

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

"Tim C" wrote in message
...
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


"Tim C" wrote in message
...
One way:
=A1-B1-0.6*((MOD(B1,1)-MOD(A1,1))0.01)

Tim C

"T" wrote in message
...
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 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)

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

"RagDyer" wrote in message
...

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

Regards,

RD

"Tim C" wrote in message
...

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

"Tim C" wrote in message
...

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

Tim C

"T" wrote in message
...
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

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" wrote:

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

thanks
"T" wrote:

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" wrote:

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