Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet change time format
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Worksheet change time format
Hi all
I have looked through all the related questions, and found most of my answer
on the cpearson website. You've nearly solved my problem except...
I can't figure out how to change the code to enter seconds every time. I've
changed Case 1 and 2 and they work fine, its the rest that I can't work out
how to change. I've managed to get the seconds in the right place in case 3,
but the minutes go into the hours column. I haven't attempted to change the
other cases as yet.
This is for a winch challenge scoring program which is a time based event.
It would be great if I could also have the .seconds also?

You guys are great
Thanks
KiwiGirl

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

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("D3:D41,J3:J41,P3:P41,V3:V41,AB3:AB41,AH3:AH 41,AN3:AN41,AT3:AT41,AZ3:AZ41,BF3:BF41,BL3:BL41,BR 3:BR41,BX3:BX41,CD3:CD41,CJ3:CJ41,CP3:CP41,DB3:DB4 1,DH3:DH41,DN3:DN41")) 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
If Target.Value = "dnf" Then
Exit Sub
End If
If Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:0" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":0:00" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 2, 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default Worksheet change time format
Hi KiwiGirl

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D3:D41, J3:J41, P3:P41, V3:V41,
AB3:AB41, AH3: AH41 , AN3: AN41 , AT3: AT41 , AZ3: AZ41 , BF3: BF41 , BL3:
BL41 , BR3: BR41 , BX3: BX41 , CD3: CD41 , CJ3: CJ41 , CP3: CP41 , DB3:
DB41 , DH3: DH41 , DN3: DN41 ")) 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
If Target.Value = "dnf" Or Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" _
& Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = "00:" & 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
.Value = ""
GoTo EndMacro
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

Regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Hi all
I have looked through all the related questions, and found most of my
answer
on the cpearson website. You've nearly solved my problem except...
I can't figure out how to change the code to enter seconds every time.
I've
changed Case 1 and 2 and they work fine, its the rest that I can't work
out
how to change. I've managed to get the seconds in the right place in case
3,
but the minutes go into the hours column. I haven't attempted to change
the
other cases as yet.
This is for a winch challenge scoring program which is a time based event.
It would be great if I could also have the .seconds also?

You guys are great
Thanks
KiwiGirl

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

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("D3:D41,J3:J41,P3:P41,V3:V41,AB3:AB41,AH3:AH 41,AN3:AN41,AT3:AT41,AZ3:AZ41,BF3:BF41,BL3:BL41,BR 3:BR41,BX3:BX41,CD3:CD41,CJ3:CJ41,CP3:CP41,DB3:DB4 1,DH3:DH41,DN3:DN41"))
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
If Target.Value = "dnf" Then
Exit Sub
End If
If Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:0" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":0:00" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 2, 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.programming
external usenet poster
  
Posts: 3
Default Worksheet change time format
Hi Per
Thanks for answering so quickly.
I copied your code and now when I enter 213 for example, I get 0:00:00 in
the cell.
Is there a special format I need to apply also. Currrently at h:mm:ss
Much appreciated.... :-)
KiwiGirl



"Per Jessen" wrote:

Hi KiwiGirl

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D3:D41, J3:J41, P3:P41, V3:V41,
AB3:AB41, AH3: AH41 , AN3: AN41 , AT3: AT41 , AZ3: AZ41 , BF3: BF41 , BL3:
BL41 , BR3: BR41 , BX3: BX41 , CD3: CD41 , CJ3: CJ41 , CP3: CP41 , DB3:
DB41 , DH3: DH41 , DN3: DN41 ")) 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
If Target.Value = "dnf" Or Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" _
& Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = "00:" & 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
.Value = ""
GoTo EndMacro
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

Regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Hi all
I have looked through all the related questions, and found most of my
answer
on the cpearson website. You've nearly solved my problem except...
I can't figure out how to change the code to enter seconds every time.
I've
changed Case 1 and 2 and they work fine, its the rest that I can't work
out
how to change. I've managed to get the seconds in the right place in case
3,
but the minutes go into the hours column. I haven't attempted to change
the
other cases as yet.
This is for a winch challenge scoring program which is a time based event.
It would be great if I could also have the .seconds also?

You guys are great
Thanks
KiwiGirl

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

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("D3:D41,J3:J41,P3:P41,V3:V41,AB3:AB41,AH3:AH 41,AN3:AN41,AT3:AT41,AZ3:AZ41,BF3:BF41,BL3:BL41,BR 3:BR41,BX3:BX41,CD3:CD41,CJ3:CJ41,CP3:CP41,DB3:DB4 1,DH3:DH41,DN3:DN41"))
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
If Target.Value = "dnf" Then
Exit Sub
End If
If Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:0" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":0:00" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 2, 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Worksheet change time format
If Application.Intersect(Target, Range
"D3:D41,J3:J41,P3:P41,V3:V41,AB3:AB41,AH3:AH41,AN 3:AN41,AT3:AT41,AZ3:AZ41,BF3:BF41,BL3:BL41,BR3:BR4 1,BX3:BX41,CD3:CD41,CJ3:CJ41,CP3:CP41,DB3:DB41,DH3 :DH41,DN3:DN41"))
Is Nothing Then

Just thought you might find it interesting to know that instead of typing in
all those range addresses in, you could have done this...

' Declare these variable with the rest of your Dim statements
Dim X As Long
Dim R As Range
'.....
'.....
Set R = Range("D3:D41")
For X = 1 To 19
Set R = Union(R, R.Offset(0, 6))
Next
If Intersect(Target, R) Is Nothing Then Exit Sub
'
' Put the rest of your code here
'

In the above code, R would end up a reference to the range you created by
typing all those addresses in.

--
Rick (MVP - Excel)

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default Worksheet change time format
Hi KiwiGirl

No special format needed.
Check if the cell where the number is entered is intersecting with the
desired range.

Regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Hi Per
Thanks for answering so quickly.
I copied your code and now when I enter 213 for example, I get 0:00:00 in
the cell.
Is there a special format I need to apply also. Currrently at h:mm:ss
Much appreciated.... :-)
KiwiGirl



"Per Jessen" wrote:

Hi KiwiGirl

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D3:D41, J3:J41, P3:P41, V3:V41,
AB3:AB41, AH3: AH41 , AN3: AN41 , AT3: AT41 , AZ3: AZ41 , BF3: BF41 ,
BL3:
BL41 , BR3: BR41 , BX3: BX41 , CD3: CD41 , CJ3: CJ41 , CP3: CP41 ,
DB3:
DB41 , DH3: DH41 , DN3: DN41 ")) 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
If Target.Value = "dnf" Or Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" _
& Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = "00:" & 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
.Value = ""
GoTo EndMacro
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

Regards,
Per


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Worksheet change time format
Per
I had that right, but I had an error in the sub name which I couldn't see
because I had scrolled past it.
All working perfectly now....

Thank you very much... you're a life saver!!!

KiwiGirl

"Per Jessen" wrote:

Hi KiwiGirl

No special format needed.
Check if the cell where the number is entered is intersecting with the
desired range.

Regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Hi Per
Thanks for answering so quickly.
I copied your code and now when I enter 213 for example, I get 0:00:00 in
the cell.
Is there a special format I need to apply also. Currrently at h:mm:ss
Much appreciated.... :-)
KiwiGirl



"Per Jessen" wrote:

Hi KiwiGirl

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D3:D41, J3:J41, P3:P41, V3:V41,
AB3:AB41, AH3: AH41 , AN3: AN41 , AT3: AT41 , AZ3: AZ41 , BF3: BF41 ,
BL3:
BL41 , BR3: BR41 , BX3: BX41 , CD3: CD41 , CJ3: CJ41 , CP3: CP41 ,
DB3:
DB41 , DH3: DH41 , DN3: DN41 ")) 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
If Target.Value = "dnf" Or Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" _
& Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = "00:" & 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
.Value = ""
GoTo EndMacro
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

Regards,
Per



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default Worksheet change time format
Thanks for your reply. I'm glad that you found the error :-)

Best regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Per
I had that right, but I had an error in the sub name which I couldn't see
because I had scrolled past it.
All working perfectly now....

Thank you very much... you're a life saver!!!

KiwiGirl

"Per Jessen" wrote:

Hi KiwiGirl

No special format needed.
Check if the cell where the number is entered is intersecting with the
desired range.

Regards,
Per

"KiwiGirl" skrev i meddelelsen
...
Hi Per
Thanks for answering so quickly.
I copied your code and now when I enter 213 for example, I get 0:00:00
in
the cell.
Is there a special format I need to apply also. Currrently at h:mm:ss
Much appreciated.... :-)
KiwiGirl



"Per Jessen" wrote:

Hi KiwiGirl

Try this:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D3:D41, J3:J41, P3:P41,
V3:V41,
AB3:AB41, AH3: AH41 , AN3: AN41 , AT3: AT41 , AZ3: AZ41 , BF3: BF41 ,
BL3:
BL41 , BR3: BR41 , BX3: BX41 , CD3: CD41 , CJ3: CJ41 , CP3: CP41 ,
DB3:
DB41 , DH3: DH41 , DN3: DN41 ")) 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
If Target.Value = "dnf" Or Target.Value = "dns" 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:00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = "00:0" & Left(.Value, 1) & ":" _
& Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = "00:" & 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
.Value = ""
GoTo EndMacro
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

Regards,
Per




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 Format on Worksheet Change Pyrite Excel Programming 5 September 10th 08 10:56 AM
Time format via Worksheet Change Pyrite Excel Programming 5 September 9th 08 11:47 AM
Change time format from :0 to 0:0 Donald B[_2_] Excel Worksheet Functions 4 July 6th 07 03:53 AM
How to change to time format? SF Excel Programming 2 September 4th 06 11:51 AM
change format for time value Qaspec Excel Discussion (Misc queries) 1 May 23rd 05 10:25 PM


All times are GMT +1. The time now is 11:15 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement