Is there a way to enter military time in a cell (1700), and have the result formatted in hh:mm (5:00 PM)?

Is there a way to enter military time in a cell (for ex, 1700), and have the result formatted in hh:mm (for ex, 5:00 PM)?

Thanks!
Tim JackoboiceOwnerAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
For the specified range

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:O34")) Is Nothing Then
        If Len(Target) = 4 And IsNumeric(Target) Then
            Target.Value = Left(Target, 2) & ":" & Right(Target, 2)
        End If
    End If
End Sub
0
 
yo_beeDirector of Information TechnologyCommented:
By default if you set the cell or column to format as Time 5:00 PM and enter 17:00 in the cell you will see it as 5:00 PM

Is that what you are looking for or are you looking for 1700 to be converted to Standard US TIME?
0
 
Tim JackoboiceOwnerAuthor Commented:
What I'm really trying to do is just enter regular time without the colon, for a large Excel sheet for payroll every week.

I thought using military time would work, as it at saved a couple steps entering 17:00 and then having the cell formatted so the result after typing it into the cell was 5:00 PM. (I obviously just using the "1:30 PM" format for the cells.)

Is there an easier way so I can just enter time without using the : (colon)?  It's a pain entering dozens and dozens of clock-in and clock-out times, and always having to shift and click to enter a colon into every time I input. Thanks.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Saqib Husain, SyedEngineerCommented:
Can you use a helper column?
0
 
yo_beeDirector of Information TechnologyCommented:
you can do something like this.
Format the column to Time 5:00 PM and enter this formula in
=TIMEVALUE((CONCAT(LEFT(F1,2),":",MID(F1,3,2))))

Open in new window


F1 is where you enter you 1700 and G1 is where there formula is entered.
0
 
Saqib Husain, SyedEngineerCommented:
Or, you can use an event handler macro like this. This works for column 3. You can change this in the second row

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        If Len(Target) = 4 And IsNumeric(Target) Then
            Target.Value = Left(Target, 2) & ":" & Right(Target, 2)
        End If
    End If
End Sub
0
 
Tim JackoboiceOwnerAuthor Commented:
Unfortunately, I can't use a helper cell....
0
 
Tim JackoboiceOwnerAuthor Commented:
Saqib ... I'm a novice at macros and VBA. How exactly do I enter in what you suggested so this will work for me?
0
 
Tim JackoboiceOwnerAuthor Commented:
I am entering times into cells A7:O34 in one particular tab of a large Excel workbook.
0
 
Saqib Husain, SyedEngineerCommented:
Right-click on the sheet tab name
Select View code
Paste the given code in the VBA window
Close the VBA window
0
 
Tim JackoboiceOwnerAuthor Commented:
Got it. Perfect! It works ... thank you very much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.