VBA to Format Column or Cell Range to convert text 1234 to hh:mm 12:34 - and how to handle if its am/pm

[MariannMcCarthy]

I need serious help - I am trying to set up a spreadsheet so that when a user enters the time from an employees time sheet they can 10-key it in without having to enter the colon or AM / PM. I have this code below - when I enter "130" in cell I get 01:30 AM - can I get this to default to PM ? the only am times that will be used are from 6:00 AM - 11:59 AM.

Private Sub Worksheet_Change(ByVal Target As Range)
'Declare 'Start time Range

Dim rStartime As Range

' Set Start Time Column
Set rStartime = Me.Range("A1:A10")

If Not Intersect(Target, rStartime) Is Nothing Then
Application.EnableEvents = False

Target.Value = TimeSerial(Left$(Target.Value2, Len(Target.Value2) - 2), Right$(Target.Value2, 2), 0)
Application.EnableEvents = True

End If

 

[Rick Rothstein]

Give this Change event procedure a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Value = CDate(Format(Target.Value - 1200 * (Target.Value < 600), "0:00"))
    Application.EnableEvents = True
  End If
End Sub

 

[MariannMcCarthy]

Thank you so much!

I put the code in and then tried it - its giving me seconds - is there any way i can just get hours and minutes?

 

[Rick Rothstein]

Thank you so much!

I put the code in and then tried it - its giving me seconds - is there any way i can just get hours and minutes?

Do you mean it is displaying :00 at the end of the time? If so, simply select the column where these time values are being entered and format it with a Time format that only shows the hours and minutes.

 

[MariannMcCarthy]

Do you mean it is displaying :00 at the end of the time? If so, simply select the column where these time values are being entered and format it with a Time format that only shows the hours and minutes.

I made this small change to it - because I am learning VBA - and I set up the Start time column but I need an entd time column. I first - copied the entire code & pasted it after it and changed rstartime to retndtime but it had a problem with that command.

So I thought I would just expand the range & this isnt working its returning 12:00 for anytime i enter

Private Sub Worksheet_Change(ByVal Target As Range)
'Declare 'Start time Range


Dim rStartime As Range


' Set Start Time Column
Set rStartime = Me.Range("A1:A10,B1:B10")




If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
Application.EnableEvents = False
Target.Value = CDate(Format(Target.Value - 1200 * (Target.Value < 600), "0:00"))
Application.EnableEvents = True


End Sub