Entering data as time into cells

[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.

 

[Dave Peterson]

Chip Pearson has an event macro just for you:

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Gord Dibben]

Rachel

See Chip Pearson's site for event code.

http://www.cpearson.com/excel/DateTimeEntry.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Jeff Muniz]

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

 

[J.E. McGimpsey]

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.