First thank you to everyone that helps all us that are stuck!
Second, this will sound strange, but this is what the customer
requests.
Issue is that when subtracting the two times in decimal time, I get a
different number than the calculation should be.
example: 0.020833333 - 0.020833333 = -7.28584E-17 Should be ZERO!


Four variables: Start Time, Finish Time, Quanity, Speed
Start and Finish times are numbers with the cells formatted to #":"##
I.E. 12:15 pm is 1215, 1:15am is 115
Quanity is a number representing amount of pieces produced
Speed is how fast each each piece is produced

Getting the amount of time between Start(I8) and Finish(J8) times:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE (LEFT(I8,1)&":"&RIGHT
(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))

Getting the amount of time from quanity(H8) and speed(M8):
=((M8*H8)/60)/(24*60)

When either subtracting these two calculation against each other:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE (LEFT(I8,1)&":"&RIGHT
(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))-
((M8*H8)/60)/(24*60)

And these are the variables:
Start 1230, Finish 130, Speed 60, Quanity 30

I get the value:
-7.28584E-17

And it should be:
0

HELP!!! I don't know if I am doing something wrong and there is most
likely a shorter way of performing this calculation.
I am open to easier ways to calculate, Functions, whatever.
Hope I didn't lose anyone on this.

DeDBlanK wrote:
First thank you to everyone that helps all us that are stuck!
Second, this will sound strange, but this is what the customer
requests.
Issue is that when subtracting the two times in decimal time, I get a
different number than the calculation should be.
example: 0.020833333 - 0.020833333 = -7.28584E-17 Should be ZERO!
[snipped]
HELP!!! I don't know if I am doing something wrong and there is most
likely a shorter way of performing this calculation.
I am open to easier ways to calculate, Functions, whatever.
Hope I didn't lose anyone on this.

First, you need to understand why a - b < c. This is not a bug, but a
limitation of how computers store decimal data. Have a look at this for
more info:
http://www.cpearson.com/excel/rounding.htm

How much precision do you really need? Can you apply a rounding function
to the result to coerce the result to a reasonable value?

i'm offering a UDF , TimeDiff() for you. There are two vales to pass, A and
B where BA and these are "times" as you have them, eg 12:15 is 1215 in the
cell and 22:30 is 2230 in the cell
the UDF treats these as text, taking the right two characters to be minutes.

the code should be copied into a standard code module (ALT+F11,
Insert/Module)


Option Explicit
Function TimeDiff(A As String, B As String)
Dim Ahr As Long
Dim Bhr As Long
Dim Amin As Long
Dim Bmin As Long
Dim min As Long
Dim hr As Long


Ahr = CLng(Left(A, Len(A) - 2))
Amin = CLng(Right(A, 2))
Bhr = CLng(Left(B, Len(B) - 2))
Bmin = CLng(Right(B, 2))

min = Bmin - Amin
If min < 0 Then
min = min + 60
hr = -1
End If

hr = hr + Bhr - Ahr
If hr < 0 Then hr = hr + 24
TimeDiff = Format$(hr, "#") & Format$(min, "00")

End Function


"DeDBlanK" wrote in message
...
First thank you to everyone that helps all us that are stuck!
Second, this will sound strange, but this is what the customer
requests.
Issue is that when subtracting the two times in decimal time, I get a
different number than the calculation should be.
example: 0.020833333 - 0.020833333 = -7.28584E-17 Should be ZERO!


Four variables: Start Time, Finish Time, Quanity, Speed
Start and Finish times are numbers with the cells formatted to #":"##
I.E. 12:15 pm is 1215, 1:15am is 115
Quanity is a number representing amount of pieces produced
Speed is how fast each each piece is produced

Getting the amount of time between Start(I8) and Finish(J8) times:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALU E(LEFT(I8,1)&":"&RIGHT
(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))

Getting the amount of time from quanity(H8) and speed(M8):
=((M8*H8)/60)/(24*60)

When either subtracting these two calculation against each other:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALU E(LEFT(I8,1)&":"&RIGHT
(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))-
((M8*H8)/60)/(24*60)

And these are the variables:
Start 1230, Finish 130, Speed 60, Quanity 30

I get the value:
-7.28584E-17

And it should be:
0

HELP!!! I don't know if I am doing something wrong and there is most
likely a shorter way of performing this calculation.
I am open to easier ways to calculate, Functions, whatever.
Hope I didn't lose anyone on this.