Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Time entry and calculation
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 181
Default Time entry and calculation
I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub







--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 376
Default Time entry and calculation
Hi Russell

Case 4 ' e.g., 1234 = 12:34
If .Value 2359 Then GoTo EndMacro

TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)

--
Regards
Roger Govier

Russell Dawson wrote:
I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub







Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 181
Default Time entry and calculation
I meant to say it does NOT allow for input in excess of 23:59.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Russell Dawson" wrote:

I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub







--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Time entry and calculation
Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


--
Regards!
Stefi



€˛Russell Dawson€¯ ezt Ć*rta:

I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub







--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 181
Default Time entry and calculation
Thanks Roger

I substituted the original with your suggestion but it still doesn't work.

I have the cells formatted as custom h:mm.

When an input in excess of 2359 is made the message 'You did not enter a
valid time' is given.

In the formula bar it shows '12/02/1907 00:00:00' when 2600 is entered for
instance.

As you probably appreciate VBA is not my forte and I am grateful for any
assistance.

Regards

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Roger Govier" wrote:

Hi Russell

Case 4 ' e.g., 1234 = 12:34
If .Value 2359 Then GoTo EndMacro

TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)

--
Regards
Roger Govier

Russell Dawson wrote:
I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub







.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 376
Default Time entry and calculation
Hi Stefi

Excellent solution.

might I suggest one further modification, as any entry ending in 60
causes a problem whatever the input length.

I think the inclusion of this If clause at the point indicated tarps for
this.

Application.EnableEvents = False

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then


--
Regards
Roger Govier

Stefi wrote:
Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Time entry and calculation
You got suggestions for your real question, but just to save some typing, you
could use:

If Application.Intersect(Target, _
me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) Is Nothing Then
Exit Sub
End If

I like to qualify the range and the Me keyword says that this range belongs to
the object (the sheet in this case) owning the code.



Russell Dawson wrote:

I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated.
My problem now is that it does allow for input of hours worked in excess of
23:59.
Is anybody able to suggest how the code can be amended to allow for this.
Code is:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"),
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"),
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

--

Dave Peterson
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Time entry and calculation
Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


--
Regards!
Stefi



€˛Roger Govier€¯ ezt Ć*rta:

Hi Stefi

Excellent solution.

might I suggest one further modification, as any entry ending in 60
causes a problem whatever the input length.

I think the inclusion of this If clause at the point indicated tarps for
this.

Application.EnableEvents = False

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then


--
Regards
Roger Govier

Stefi wrote:
Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 181
Default Time entry and calculation
Roger, Stefi and Dave

Many thanks for your input. It works, but I don't really need to tell you
that do I?

It's humbling to have some of those whom I consider to be some of the 'big
guns' helping me out.

Kind regards.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Stefi" wrote:

Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


--
Regards!
Stefi



€˛Roger Govier€¯ ezt Ć*rta:

Hi Stefi

Excellent solution.

might I suggest one further modification, as any entry ending in 60
causes a problem whatever the input length.

I think the inclusion of this If clause at the point indicated tarps for
this.

Application.EnableEvents = False

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then


--
Regards
Roger Govier

Stefi wrote:
Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


.

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Time entry and calculation
You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated.

But I don't understand your reply! Why is it humbling for a student or for a
beginner to learn from more experienced people? We also were beginners and
learned from our teachers!

--
Regards!
Stefi



€˛Russell Dawson€¯ ezt Ć*rta:

Roger, Stefi and Dave

Many thanks for your input. It works, but I don't really need to tell you
that do I?

It's humbling to have some of those whom I consider to be some of the 'big
guns' helping me out.

Kind regards.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Stefi" wrote:

Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


--
Regards!
Stefi



€˛Roger Govier€¯ ezt Ć*rta:

Hi Stefi

Excellent solution.

might I suggest one further modification, as any entry ending in 60
causes a problem whatever the input length.

I think the inclusion of this If clause at the point indicated tarps for
this.

Application.EnableEvents = False

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then


--
Regards
Roger Govier

Stefi wrote:
Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _
Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _
Range("s:s"), Range("u:u"), _
Range("w:w"), Range("y:y"))) 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
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


.

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 376
Default Time entry and calculation
Hi Stefi

I hadn't meant my suggestion about the 60 ending to sound critical.
I realise that the original code didn't cater for it, I was just
suggesting a further addition to the excellent work you had already done.

--
Regards
Roger Govier

Stefi wrote:
Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,646
Default Time entry and calculation
Anyway, your suggestion was very useful, I never had thought of that by myself!
--
Regards!
Stefi



€˛Roger Govier€¯ ezt Ć*rta:

Hi Stefi

I hadn't meant my suggestion about the 60 ending to sound critical.
I realise that the original code didn't cater for it, I was just
suggesting a further addition to the excellent work you had already done.

--
Regards
Roger Govier

Stefi wrote:
Hi Roger,

Thanks for the appreciation. As far as 60 ending is concerned, the original
code also missed to handle this issue even with hour numbers less then 24,
and the OP didn't include it, I focused on the written request.

Nevertheless I consolidated suggestions from you and Dave, this is the final
form, though I guess that the Select Case code could be written in a more
compact way.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, _
Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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

If Right(Target.Value, 2) = "60" Then
MsgBox "Time entered cannot end in 60 (only 59)!"
Target.Value = ""
Target.Activate
Application.EnableEvents = True
Exit Sub
End If

With Target
If .HasFormula = False Then
dayno = 0
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
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
hourno = Val(Left(.Value, 1))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
hourno = Val(Left(.Value, 2))
dayno = hourno \ 24
remhour = hourno Mod 24
TimeStr = Str(remhour) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr) + dayno
.NumberFormat = "[h]:mm:ss"
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Time entry auto changes to date and then time ? Carol @ Prison[_2_] Excel Worksheet Functions 1 November 25th 09 10:01 PM
Stop time - start time calculation squack21 Excel Worksheet Functions 5 December 10th 07 03:20 PM
Time calculation (Subraction of Idle Time) Ajay Excel Discussion (Misc queries) 6 March 4th 07 11:54 AM
Ignoring Time in a Date Time Calculation nmp Excel Worksheet Functions 3 November 23rd 05 08:32 PM
time calculation with military time Ron Thetford Excel Worksheet Functions 8 July 29th 05 07:24 PM


All times are GMT +1. The time now is 06:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement