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.

On Jul 14, 10:09*am, "Patrick Molloy"
wrote:
no need to select etc ---

'Clear Production Area
* * with Sheets("Report Entry")
* * * * .Range("B6:C35").ClearContents
* * * * .Range("H6:H35").ClearContents
* * * * .Range("I6:J6").ClearContents * * '<hangs here
* * * * .Range("J7:J35").ClearContents
* * * * .Range("M6:M35").ClearContents
* * end with

thinking about the code and UDF
converting the time to minutes is a bit easier
this function returns minutes
so if A is 1215 and B is 20 *(00:20) then B is 725 minutes after A ( 725
minutes = 12h 5m)

Option Explicit
Function TimeDiff(A As String, B As String) As Long
' Input : A as text like [hh][m]m
' Input : B as text like [hh][m]m
' condition: A < B
'output minutes B is after A
Dim minsA As Long
Dim minsB As Long

If Len(A) 2 Then
* * minsA = 60 * CLng(Left(A, Len(A) - 2))
End If
If Len(A) 2 Then
* * minsA = minsA + CLng(Right(A, 2))
Else
* * minsA = minsA + CLng(A)
End If

If Len(B) 2 Then
* * minsB = 60 * CLng(Left(B, Len(B) - 2))
End If
If Len(B) 2 Then
* * minsB = minsB + CLng(Right(B, 2))
Else
* * minsB = minsB + CLng(B)
End If

TimeDiff = minsB - minsA
If TimeDiff < 0 Then
* * TimeDiff = TimeDiff + (24 * 60)
End If

End Function

"DeDBlanK" wrote in message

...



On Jul 14, 8:04 am, "Patrick Molloy"
wrote:
doesn't sound right. What is the formula in CF?

"DeDBlanK" wrote in message

....

On Jul 13, 11:41 am, DeDBlanK wrote:
On Jul 12, 11:50 am, "Patrick Molloy"
wrote:

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.- Hide quoted text -

- Show quoted text -

Sorry for the late response, I have been on vacation.

Thank you for your help in the explaining what the issue was smartin.

OK, I fixed the issue by dropping the UDF and placing some side
formulas using some of the concepts in TimeDiff UDF.
I added columns off to the side that calculate each row's Qty*Speed/
60, Calculated time different by min then adding the hours to the
minutes, and finally a column to get the difference between the Result
(Qty*Speed/60) - Result(FinishTime-StartTime).
I would like to figure out why the UDF would cause the CLEAR CONTENTS
to stop when the UDF was in the Conditional Formatting. Any ideas
would be greatly appreciated.
Thank you again for helping me out Mr. Molloy. I can't thank people
like you enough.