Entering data as time into cells

  • Thread starter Thread starter Rachel H
  • Start date Start date
R

Rachel H

I have to do payroll, and I would like to know how you
can format a cell so that you do not have to enter a :
when entering times, i.e. for 7:40am I would like to just
enter 740 or 0740.
 
I want to do something similar, but I'm entering run times, so I want it to
automatically go to minutes and seconds. So 9:30 would be 9 minutes and 30
seconds. Or 10:25 would be 10 minutes and 25 seconds.

Jeff
 
Formatting can't change the way the parser interprets entries. One
way would be to enter the times as hours and minutes and use an
event macro to divide each cell by 60. Put this in the Worksheet
code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = .Value / 60
.NumberFormat = "mm:ss"
End With
Application.EnableEvents = True
End If
End Sub

Change the range as necessary.

Another way, though it's a bit more difficult - if you enter times
with a decimal point:

10:25.0

XL's parser will interpret the entry as minutes and seconds.
 
Back
Top