Insert Colon and Still Have Date in the Same Column

jsnyder57

New Member
Joined
Mar 21, 2013
Messages
7
Hi All I have a scheduler that contains a date and several rows containing time in the same column. I am currently using the following VBA code to insert a colon in the correct spot

Private Sub Worksheet_ChangeDate(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
UserInput = Target.Value
If UserInput > 99
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

And the code inputs a colon in between the 20 and 13 for the year. I tried using the next code, but none of the code works (we tried using msgboxes to test the code and none of the boxes popped up)

Private Sub Worksheet_ChangeDate(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
UserInput = Target.Value
If UserInput > 99 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
ElseIf UserInput = 2013 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & "/" & Mid(UserInput, 2) & "/" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

Does anyone have any suggestions to make this code work?

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What does your input look like? You probably want the Worksheet Change event though. There is no such thing as Worksheet_ChangeDate
 
Upvote 0
Hi All I have a scheduler that contains a date and several rows containing time in the same column. I am currently using the following VBA code to insert a colon in the correct spot

<<<...code snipped...>>>

And the code inputs a colon in between the 20 and 13 for the year. I tried using the next code, but none of the code works (we tried using msgboxes to test the code and none of the boxes popped up)

<<<...code snipped...>>>
Show as a sample listing of what you have in Column B (that is the column with the date and times in them, right?).
 
Upvote 0
How about using ISDATE to see if the value is a date or not?
Without seeing any values, can't tell you if it will work.
 
Upvote 0
Here is a snip of columns A & B

Date:3/29/2013
#Month:4
#Day:1
Status/Room:IP
Time Sch.:unscheduled
Time In:8:20
Time Start:8:26
Time End:9:20
Time Out:9:35
Time Antibiotic8:20
Time Born:8:48

<colgroup><col><col></colgroup><tbody>
</tbody>

I have never used ISDATE, but I will give it a try.
 
Upvote 0
Here is a snip of columns A & B
What exactly is the problem with your data? Your orginal message said your code inserted colons but that it did so for the years also... for the data snippet you are showing us, the times already have colons and the date looks fine. So what is the problem you are having? Or did you simply show us how you want your data to look? If that is what you did, it does not help us any... we need to see your raw untouched data in order to see what needs to be done to it to make it look like you actually want. That means we need to see a sample of your before data and, for that same sample data, what you want it to look like after the macro runs.
 
Last edited:
Upvote 0
Sorry I took a sample what I wanted it to look like. After searching for awhile this morning I finally found someone who had the same problem that I had and used this code instead to get the fix.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

'Define the range where you want the code to work (our example is "C:G").
'Change within the " marks
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "hh:mm"
Case 100 To 2399
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "hh:mm"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 240000 To 245959
.Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub

This allows me to enter in the date and other non time numbers without converting everything to HH:MM

Thank you all for your feedback.
 
Upvote 0
And if that's the case, you don't need all those Select Cases, etc.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, e
Set d = Intersect(Target, Range("B:B"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If IsNumeric(c) And c > 0 Then
        e = Int(c)
        If e < 10000 Then
            e = Format(e, "00\:00")
            c.NumberFormat = "hh:mm"
        Else
            e = Format(e, "00\:00\:00")
            c.NumberFormat = "hh:mm:ss"
        End If
        c = e
    End If
Next
Application.EnableEvents = True
End Sub
 
Upvote 0
And if that's the case, you don't need all those Select Cases, etc.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, e
Set d = Intersect(Target, Range("B:B"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    If IsNumeric(c) And c > 0 Then
        e = Int(c)
        If e < 10000 Then
            e = Format(e, "00\:00")
            c.NumberFormat = "hh:mm"
        Else
            e = Format(e, "00\:00\:00")
            c.NumberFormat = "hh:mm:ss"
        End If
        c = e
    End If
Next
Application.EnableEvents = True
End Sub
Actually, given that plus the fact that the word "Time" starts each cell in Column A where the OP wants to be able to type in a number and have it converted to a time value, I think this code should also work for the OP...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim TextVal As String
  If Target.Count = 1 Then
    If Target.Column = 2 Then
      If Target.Offset(, -1).Value Like "Time*" Then
        If Not Target.Value Like "*[!0-9]*" Then
          Application.EnableEvents = False
          Target.NumberFormat = "General"
          Target.Value = Application.Text(--Target.Value, "[<2400]0\:00;0\:00\:00")
          Application.EnableEvents = True
        End If
      End If
    End If
  End If
End Sub
Note: My code assumes the user will be entering data in one cell at a time as opposed to copying some unformatted range of cell values into multiple cells all-at-once as your code does.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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