24 Hour time formatting

DebbieW

New Member
Joined
Dec 21, 2005
Messages
8
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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.
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

New Member
Joined
Dec 21, 2005
Messages
8
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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 ...
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
    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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Legend
Joined
Feb 18, 2002
Messages
13,690
OK, Here's the fix
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,000
Members
412,353
Latest member
SofiaV
Top