Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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!
0
Tim Jackoboice
Asked:
Tim Jackoboice
  • 5
  • 4
  • 2
1 Solution
 
yo_beeDirector of ITCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Can you use a helper column?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
yo_beeDirector of ITCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Tim JackoboiceOwnerAuthor Commented:
Got it. Perfect! It works ... thank you very much!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now