Calculation Nightmares!

Frith

Board Regular
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.
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.

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

A1: Start time
B1: End time

Try...

=B1-A1+(B1 < A1)

with the formula cell custom formatted as: [h]:mm

Aladin, just curious, can't you just use =B1-A1, what does +(B1 < A1) do?

Aladin, just curious, can't you just use =B1-A1, what does +(B1 < A1) do?

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.

Thanks, I see that now with just using time, I was thinking about using Now to get the date and time.

Thanks, I see that now with just using time, I was thinking about using Now to get the date and time.

When both date and time are recorded, the issue does indeed not arise.

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``````

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

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``````

Replies
8
Views
391
Replies
6
Views
170
Replies
6
Views
209
Replies
8
Views
364
Replies
6
Views
260

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

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.

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

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