Entering time of day

  • Thread starter Thread starter Kevin G
  • Start date Start date
K

Kevin G

Is there a way to format a cell so you can enter the time
without having to enter "AM" or "PM" after it or use
the ":" symbol? Like just type "1345" and it would know
that is 13:45 or 1:45 PM and do time calcs correctly. I
am trying to simplify entering times in our timesheets.
 
Kevin,

Its easy with VBA. Are you up for VBA? If so, give us the range that it
applies to.
 
You may like this. Right click on sheet tab>view code>insert this
It will work for rows 5 and below in column b or c. Uncomment the commented
cells to do calculations.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row > 5 And Target.Column = 2 And Target > 1 Or Target.Row > 5 And
Target.Column = 3 And Target > 1 Then
Target = Format(Left(Target.Value, 2) & ":" & Right(Target.Value, 2),
"hh:mm")
'Cells(Target.Row, 4) = Format(Cells(Target.Row, 3) - Cells(Target.Row, 2),
"hh:mm")
'Cells(Target.Row, 5) = (Cells(Target.Row, 4) * 1440) * ([d4] / 60)
End If
Application.EnableEvents = True
End Sub

Sub fixit()'in case above stops working
Application.EnableEvents = True
End Sub
 
Back
Top