Issue with numbers and Time
DeDBlanK
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!
> 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
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" <dedb...@gmail.com> wrote in message
news:1c0b822f-10e8-4622...@r16g2000vbn.googlegroups.com...
DeDBlanK
wrote:
>
> - 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
(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
>> If hr < 0 Then hr = hr + 24
good that you checked though
cheers
"DeDBlanK" <dedb...@gmail.com> wrote in message
news:eeca9406-cd69-446e...@e18g2000vbe.googlegroups.com...
DeDBlanK
>
> - 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
"DeDBlanK" <dedb...@gmail.com> wrote in message
news:d67e06cf-aab2-415c...@p15g2000vbl.googlegroups.com...
DeDBlanK
wrote:
> 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
'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" <dedb...@gmail.com> wrote in message
news:deea92e4-7fb0-4d7d...@p28g2000vbn.googlegroups.com...
DeDBlanK
wrote:
>
> - Show quoted text -
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
Sorry Something happened to the post?...
DeDBlanK
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.