Avatar of Cactus1993
Cactus1993
Flag for United States of America asked on

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!
Microsoft Excel

Avatar of undefined
Last Comment
Cactus1993

8/22/2022 - Mon
yo_bee

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?
Cactus1993

ASKER
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.
Saqib Husain

Can you use a helper column?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
yo_bee

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.
Saqib Husain

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
Cactus1993

ASKER
Unfortunately, I can't use a helper cell....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Cactus1993

ASKER
Saqib ... I'm a novice at macros and VBA. How exactly do I enter in what you suggested so this will work for me?
Cactus1993

ASKER
I am entering times into cells A7:O34 in one particular tab of a large Excel workbook.
Saqib Husain

Right-click on the sheet tab name
Select View code
Paste the given code in the VBA window
Close the VBA window
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Cactus1993

ASKER
Got it. Perfect! It works ... thank you very much!!