Issue with numbers and Time

[DeDBlanK]

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(LEFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(IF((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)&":"&RIGHT(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)&":"&RIGHT(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(LEFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(IF((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)&":"&RIGHT(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)&":"&RIGHT(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.

 

[smartin]

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?

 

[Patrick Molloy]

i'm offering a UDF , TimeDiff() for you. There are two vales to pass, A and
B where B>A 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]

i'm offering a UDF , TimeDiff() for you. There are two vales to pass, A and
B where B>A 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












- 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.
That's an eye opener. Never put real thought into the depth of how
computers deal with infinite remainders.

Patrick, I made some changes to your code to accomidate for string
lengths that are less than three and checking for 12 hr time format
issue. This function works in my spreadsheet.
****************************************CODE***********************************
Option Explicit
Function TimeDiff(A As String, B As String)
Dim Ahr As Long 'A start time
Dim Bhr As Long 'B finish time
Dim Amin As Long
Dim Bmin As Long
Dim min As Long
Dim hr As Long
Dim hrFix As Long

'Check A string for 4 characters
If Len(A) > 3 Then
Ahr = CLng(Left(A, Len(A) - 1))
Else
Ahr = CLng(Left(A, Len(A) - 2))
End If

Amin = CLng(Right(A, 2))


'Check B string for 4 characters
If Len(B) > 3 Then
Bhr = CLng(Left(B, Len(B) - 1))
Else
Bhr = CLng(Left(B, Len(B) - 2))
End If

Bmin = CLng(Right(B, 2))

'Check Amin and Bmin for hour change
If Bmin < Amin Then
min = Bmin - Amin + 60
hr = -1
Else
min = Bmin - Amin
End If

'check for 12 hour diff
If B < A Then
hrFix = 12 'don't know if I should set the value to zero if
false
End If

'Combined calcs
hr = hr + (Bhr - Ahr) + hrFix


TimeDiff = Format$(hr, "#") & Format$(min, "00")


End Function

************************************END
CODE*****************************

 

[Patrick Molloy]

two assumptions that I'd made were
(1) time would be hmm so not less than 3 characters ... however, you're
correct 00:15 would be 15 interbnally and this be two characters
(2) B would always be AFTER A. that way we can diff 0115 as a later time
than 0715 and hence my 24 hour adjustment
good that you checked though

cheers

 

[DeDBlanK]

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

Thank you for your help in the explaining what the issue was smartin.
That's an eye opener.  Never put real thought into the depth of how
computers deal with infinite remainders.

Patrick, I made some changes to your code to accomidate for string
lengths that are less than three and checking for 12 hr time format
issue.  This function works in my spreadsheet.
****************************************CODE*******************************­****
Option Explicit
Function TimeDiff(A As String, B As String)
    Dim Ahr As Long 'A start time
    Dim Bhr As Long 'B finish time
    Dim Amin As Long
    Dim Bmin As Long
    Dim min As Long
    Dim hr As Long
    Dim hrFix As Long

'Check A string for 4 characters
    If Len(A) > 3 Then
        Ahr = CLng(Left(A, Len(A) - 1))
    Else
        Ahr = CLng(Left(A, Len(A) - 2))
    End If

    Amin = CLng(Right(A, 2))

'Check B string for 4 characters
    If Len(B) > 3 Then
        Bhr = CLng(Left(B, Len(B) - 1))
    Else
        Bhr = CLng(Left(B, Len(B) - 2))
    End If

    Bmin = CLng(Right(B, 2))

'Check Amin and Bmin for hour change
    If Bmin < Amin Then
        min = Bmin - Amin + 60
        hr = -1
    Else
        min = Bmin - Amin
    End If

    'check for 12 hour diff
    If B < A Then
        hrFix = 12  'don't know if I should set the value to zero if
false
    End If

    'Combined calcs
    hr = hr + (Bhr - Ahr) + hrFix

    TimeDiff = Format$(hr, "#") & Format$(min, "00")

End Function

************************************END
CODE*****************************- Hide quoted text -

- Show quoted text -

OK, another issue is occuring when the Function TimeDiff is in place.
I have a CLEAR button on the sheet that clear all the data out on the
report. It has stopped working when the function of TimeDiff is being
used in Conditional Formatting. This has me boggled. I have tried it
without the Function TimeDiff and it works fine (meaning that the
function is imported, but the it is not being used in the Conditional
Formatting). As soon it's placed back into the Conditional
Formatting, The CLEAR script stops at that cell. Any ideas? Is there
something I don't know about Conditional Formatting?

 

[Patrick Molloy]

doesn't sound right. What is the formula in CF?

OK, another issue is occuring when the Function TimeDiff is in place.
I have a CLEAR button on the sheet that clear all the data out on the
report. It has stopped working when the function of TimeDiff is being
used in Conditional Formatting. This has me boggled. I have tried it
without the Function TimeDiff and it works fine (meaning that the
function is imported, but the it is not being used in the Conditional
Formatting). As soon it's placed back into the Conditional
Formatting, The CLEAR script stops at that cell. Any ideas? Is there
something I don't know about Conditional Formatting?

 

[DeDBlanK]

doesn't sound right. What is the formula in CF?







- Show quoted text -

One other thing since yesterday, I changed the TimeDiff formula to:
TimeDiff = hr * 60 + min
I needed to compair minutes to minutes. This didn't change the hang
though.

The conditional format is:
=TimeDiff(I6,J6)-(H6*M6)/60<0
H6 is Number of parts, M6 is Amount of seconds
All this does is check if the time they enter is not less than the
time they are allowed.
i.e.
(Units Produced * Amount of Seconds to produce Each Part)/60 'to
convert to minutes

I stepped through the code again this morning and it hangs on the
first cell with that conditional format.
Funny thing is that I don't get an error code, break, anything. It's
almost like it's stuck in a loop.
The code for the Macro is simple. This is where it hangs:
'Clear Production Area
Sheets("Report Entry").Select
Range("B6:C35").Select
Selection.ClearContents
Range("H6:H35").Select
Selection.ClearContents
Range("I6:J6").Select '<cf applied here
Selection.ClearContents '<hangs here
Range("J7:J35").Select
Selection.ClearContents
Range("M6:M35").Select
Selection.ClearContents

 

[Patrick Molloy]

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]

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

One other thing since yesterday, I changed the TimeDiff formula to:
TimeDiff = hr * 60 + min
I needed to compair minutes to minutes.  This didn't change the hang
though.

The conditional format is:
=TimeDiff(I6,J6)-(H6*M6)/60<0
H6 is Number of parts, M6 is Amount of seconds
All this does is check if the time they enter is not less than the
time they are allowed.
i.e.
(Units Produced * Amount of Seconds to produce Each Part)/60 'to
convert to minutes

I stepped through the code again this morning and it hangs on the
first cell with that conditional format.
Funny thing is that I don't get an error code, break, anything.  It's
almost like it's stuck in a loop.
The code for the Macro is simple.  This is where it hangs:
'Clear Production Area
   Sheets("Report Entry").Select
   Range("B6:C35").Select
   Selection.ClearContents
   Range("H6:H35").Select
   Selection.ClearContents
   Range("I6:J6").Select         '<cf applied here
   Selection.ClearContents     '<hangs here
   Range("J7:J35").Select
   Selection.ClearContents
   Range("M6:M35").Select
   Selection.ClearContents- Hide quoted text -

- Show quoted text -

Mr. Molloy,
Thank you for your help. It is greatly appreciated.
I don't think I am being clear about the times, so I am going to
reitterate;
A would be a start time
B would be a finish time
the format in each cell is custom #":"##
Data validation on each cell is set to WHOLE NUMBER
between 100 and 1259
A & B are 12hr format minus the AM and PM, but I still need to know
when a time lapses.
This is where the 12hr time issue comes into play. I had this in the
code that I modified from you original post. The new code that you
sent does not include this.
I.E. A=1259 & B=100
Your code A-B=721 My code A-B=1

BTW, I didn't design the original worksheet. I just place limits in
the cells to prevent erroranous entries to get what they want.

Anyways, I am still having the Hang issue with your code also.

 

[DeDBlanK]

'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

- Show quoted text -

Mr. Molloy,
Thank you for your help.  It is greatly appreciated.
I don't think I am being clear about the times, so I am going to
reitterate;
A would be a start time
B would be a finish time
the format in each cell is custom #":"##
Data validation on each cell is set to WHOLE NUMBER
between 100 and 1259
A & B are 12hr format minus the AM and PM, but I still need to know
when a time lapses.
This is where the 12hr time issue comes into play.  I had this in the
code that I modified from you original post.  The new code that you
sent does not include this.
I.E. A=1259 & B=100
Your code A-B=721  My code A-B=1

BTW, I didn't design the original worksheet. I just place limits in
the cells to prevent erroranous entries to get what they want.

Anyways, I am still having the Hang issue with your code also.

Sorry Something happened to the post?...
Mr. Molloy,
Thank you for your help. It is greatly appreciated.
I don't think I am being clear about the times, so I am going to
reitterate;
A would be a start time
B would be a finish time
the format in each cell is custom #":"##
Data validation on each cell is set to WHOLE NUMBER
between 100 and 1259
A & B are 12hr format minus the AM and PM, but I still need to know
when a time lapses.
This is where the 12hr time issue comes into play. I had this in the
code that I modified from you original post. The new code that you
sent does not include this.
I.E. A=1259 & B=100
Your code A-B=721 My code A-B=1

BTW, I didn't design the original worksheet. I just place limits in
the cells to prevent erroranous entries to get what they want.


Anyways, I am still having the Hang issue with your code also.

 

[DeDBlanK]

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.