Time Entry Issue

[Fireman1224]

Tried to follow the instructions:

http://www.youtube.com/user/bjele123#p/c/76C979CEFD1A4B30/42/FVTB6ruazJU

but I can't get the proper time to display..it works for times >10:00, but 00:00 to 09:59 is weird. Here is the code I used in Excel 2007

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then
Exit Sub

ElseIf Target.Column = 16 Then
Application.EnableEvents = False
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Application.EnableEvents = True
End If
End Sub

I also want to replace Target.Column =16 to all cells within a named range "Time".

Please Help!

 

[btadams]

The code below will answer your 2nd request. What is it that is weird about times < 10:00?

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsEmpty(Target) Then
        Exit Sub
    ElseIf Not Intersect(Target, Range("Time")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
        Application.EnableEvents = True
    End If
End Sub

 

[Fireman1224]

Thanks for the range fix, here's what I get with <1000 entry...

0700 = 1/2/1900 10:00:00 PM
0100 = 10:00:00 AM
0300 = 1/1/1900 6:00:00 AM

I don't understand the pattern...

 

[btadams]

Do the cells have the same formatting?

Also, was 0100 supposed to be 1000?

 

[Fireman1224]

Yes. All cells in the range "Time" have the formatting foe hh:mm.

0030 = 00:30
0100 = 01:00
1000 = 10:00
etc...

Thanks!

 

[btadams]

Have a look here:

http://www.mvps.org/dmcritchie/excel/datetime.htm

There is a macro named Fixhhmm that looks like it might do what you want.