Calculation Nightmares!

F

Frith

Board Regular
Joined
Nov 1, 2009
Messages
99
Hi,

The below formula calculates the time, in hours/minutes, elapsed between two different times (i.e., start time of 12:00 TO end time of 12:02 = "00:02" elapsed time). However it only "SOMETIMES" works. When it doesn't, it will always resolve to "1 minute" less than what it should be (i.e., "00:01", in the above example).

I've tried just about every possible "format" known to Excel 2003, and NOTHING seems to fix it... Any ideas on what could possibly be the issue here?!

=a1+(b1>a1)-b1

Thanks,
Frith
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
what is the actual formula you are using now, and what are the input values. it sounds like a rounding error to me. you could try adding 59 seconds for example.
 
Upvote 0
Frith said:
Hi,

The below formula calculates the time, in hours/minutes, elapsed between two different times (i.e., start time of 12:00 TO end time of 12:02 = "00:02" elapsed time). However it only "SOMETIMES" works. When it doesn't, it will always resolve to "1 minute" less than what it should be (i.e., "00:01", in the above example).

I've tried just about every possible "format" known to Excel 2003, and NOTHING seems to fix it... Any ideas on what could possibly be the issue here?!

=a1+(b1>a1)-b1

Thanks,
Frith
Click to expand...

A1: Start time
B1: End time

Try...

=B1-A1+(B1 < A1)

with the formula cell custom formatted as: [h]:mm
 
Upvote 0
dee101 said:
Aladin, just curious, can't you just use =B1-A1, what does +(B1 < A1) do?
Click to expand...

If the two times fall in the same day, that would be okay. But with:

A1: 02:00 PM
B1: 06:00 AM

The result should be 16 hours. If B1 < A1 is TRUE, 1 (i.e., 12 hours) is added to the difference.
 
Upvote 0
Thanks, I see that now with just using time, I was thinking about using Now to get the date and time.
 
Upvote 0
Thanx all,

This, of course, would work except that the below code (the "timer" portion of it, at the top and near the bottom) is somehow throwing off the results by -1 minute. Try and you'll see... Any thoughts on how to resolve???

Frith

Code: 
Private RunWhen As Double
Private Sub Worksheet_Change(ByVal Target As Range)
 
'Restart timer
 
StopTimer
StartTimer
Dim TimeStr As String
With Target
If .Column = 2 Then
If Len(.Offset(, 1)) = 0 Then
.Offset(, 1) = Now
.Offset(, 3) = Now
End If
End If
End With
If Target.Column = 6 Then
Target.Offset(, -1) = Now
End If
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C3:D50, E3:F50, q1:q2")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
Application.EnableEvents = True
End Sub
 
Private Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, 999)
Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=True
End Sub
Private Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=False
End Sub
Private Sub SelectCell()
'Select last non-blank cell in column B
 
Cells(Rows.Count, "B").End(xlUp).Select
 
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B3:B50")) Is Nothing And IsEmpty(Target(1).Value) Then
If Target.Count < 5 Then
On Error Resume Next
ActiveSheet.PasteSpecial NoHTMLFormatting:=True
If Err Then
Err.Clear
Target.PasteSpecial xlPasteAll
ClearClipboard
End If
End If
ClearClipboard
End If
ClearClipboard
 
If Not Intersect(Target, Range("D3:D50,F3:F50")) Is Nothing Then
If Target.Value = vbNullString And Range("A50").Value = vbNullString Then
Target.Value = Format(Now, "ttttt")
End If
End If
 
End Sub
 
Upvote 0
Any alternatives to that "timer" function (it just bounces the cursor back to the last filled cell in column B five sec. after the last cell input/edit, which I actually kinda need) that keepe screwing up the above simple formula?

Frith

Frith said:
Thanx all,

This, of course, would work except that the below code (the "timer" portion of it, at the top and near the bottom) is somehow throwing off the results by -1 minute. Try and you'll see... Any thoughts on how to resolve???

Frith

Code: 
Private RunWhen As Double
Private Sub Worksheet_Change(ByVal Target As Range)
 
'Restart timer
 
StopTimer
StartTimer
Dim TimeStr As String
With Target
If .Column = 2 Then
If Len(.Offset(, 1)) = 0 Then
.Offset(, 1) = Now
.Offset(, 3) = Now
End If
End If
End With
If Target.Column = 6 Then
Target.Offset(, -1) = Now
End If
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C3:D50, E3:F50, q1:q2")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
Application.EnableEvents = True
End Sub
 
Private Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, 999)
Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=True
End Sub
Private Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=False
End Sub
Private Sub SelectCell()
'Select last non-blank cell in column B
 
Cells(Rows.Count, "B").End(xlUp).Select
 
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B3:B50")) Is Nothing And IsEmpty(Target(1).Value) Then
If Target.Count < 5 Then
On Error Resume Next
ActiveSheet.PasteSpecial NoHTMLFormatting:=True
If Err Then
Err.Clear
Target.PasteSpecial xlPasteAll
ClearClipboard
End If
End If
ClearClipboard
End If
ClearClipboard
 
If Not Intersect(Target, Range("D3:D50,F3:F50")) Is Nothing Then
If Target.Value = vbNullString And Range("A50").Value = vbNullString Then
Target.Value = Format(Now, "ttttt")
End If
End If
 
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
ODD DATE & TIME (FORMAT) CALCULATION
Replies
8
Views
391
Tetra201
Tetra201
J
  • Question
Calculate Hours Across Different Dates & Times
Replies
6
Views
170
Proulxs
P
matrix26
  • Solved
VBA Macro
Replies
6
Views
209
Peter_SSs
Peter_SSs
G
  • Solved
Calculating time differences regardless of date
Replies
8
Views
364
Dave Patton
D
M
  • Solved
Date and Time Calculation Based on Sun Raise and End of Night
Replies
6
Views
260
mba_110
M

Forum statistics

Threads
1,203,528
Messages
6,055,930
Members
444,835
Latest member
Jonaskr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top