24 Hour time formatting

[DebbieW]

Hi everyone. Can someone please let me know how I can type 2100 into a cell and have it automatically revert to 21:00. I will be entering "time" into cells in a 24hr format. eg, 2300, 1800, etc.

Thank you

Debbie

 

[lenze]

Hi Debbie
Welcome to the board

To do this easily, you will need to use code
Assumming your times are being entered in column A (ie Col 1) you can use this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    .Value = Left(.Value, 2) & ":" & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

This goes in the worksheet module. Right click sheet tab and choose view code. Paste in the panel on the right. Adjust colulm number as needed or use an intersect. Repost if you need help.

lenze

 

[DebbieW]

Thank you Lenze for your very quick response. I have applied your code, but there is one small problem. I do not want the whole of the column to be formatted this way, I only want certain cells to work this way. They are random cells. I confirm that this formula is only applicable to one column, but as I said, not every cell in that column will need that formula. I hope I have made sense in how I have explained it. I would be very grateful if you can let me know if this can be done.

Debbie

 

[Yogi Anand]

Hi Debbie:

I don't see Lenze around. So, let me take a look at it. One way to selectively translate your numeric entries into true time would be to have a unique qualifier, let us say the letter 'c' for convert as the fifth character of the entry that should be converted to true time ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Len(Target) = 5 And Right(Target, 1) = "c" Then
        With Target
        .Value = Left(.Value, 2) & ":" & Mid(.Value, 3, 2)
        End With
     End If
    Application.EnableEvents = True
End Sub

I hope this helps!

 

[lenze]

You can still use my code via an INTERSECT statement. Select all the cells where the code is to apply. Use the control key to select non-contiguous cells. With these selected, Choose Insert>Name>Define. Enter a name (say timeCells) and click OK. Now change
If Target.Column <> 1 Then Exit Sub to

If Intersect(Target,Range("timeCells")) Is Nothing Then Exit Sub
Now the code will only execute when one of these cells is changed.

That said, there seems to be a problem with single digit hours. I'm checking on it.

lenze

 

[lenze]

OK, Here's the fix

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target,Range("timeCells")) Is Nothing Then Exit Sub 
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    If Len(Target) = 3 Then .Value = Left(.Value, 1) & ":" & Right(.Value, 2)
    If Len(Target) = 4 Then .Value = Left(.Value, 2) & ":" & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

HTH

lenze