Excel - times

N

Nick B

Guess you could use decimals. i.e. typing in .75 would be 6:00PM and .5 would
be 12 noon and .8 would be 7:12PM, etc.

Any reason you don't want to enter a colon?

Perhaps putting hours in one column and minutes in another?

Guess you could also make a system-wide change (if using Windows) and go to
Control Panel->Regional Settings, click Customize and go to the Time tab and
change the Time Separator to something other than a colon.
 
E

Earl Kiosterud

PJ,

Something has to separate the hrs min and sec, unless you'll enter fixed
character counts (below). If you'd rather use a non-Shift character, an
event sub can do the substitution. The following sub macro, in the sheet
module, will allow you to type a semicolon instead of a colon, and will
switch them to colons. It's set up to only convert column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Hold
Dim i As Integer
Hold = ""
If Not Intersect(Target, Range("A:A")) Is Nothing Then
For i = 1 To Len(Target)
If Mid(Target, i, 1) <> ";" Then
Hold = Hold & Mid(Target, i, 1)
Else
Hold = Hold & ":"
End If
Next i
Application.EnableEvents = False
Target.Value = Hold
Application.EnableEvents = True
End If

End Sub

It's barebones. It doesn't handle non-numeric stuff (it still converts the
semicolons).

Another possible solution is to enter fixed characters counts, like 010203,
and have them converted by a similar event sub to 01:02:03. Post back, if
you're willing to use a macro.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

pj said:
In Excel - how do you enter times without having to type the colon each
Click to expand...
time
 
B

Biff

Hi!

Use Nick's idea of replacing the colon with a decimal
point only do it this way:

*TEMPORARILY* set AutoCorrect to replace a decimal point
with a colon.

Enter your time as 10.22 and it will automatically convert
to a real Excel time value. Of course, you still have to
enter the AM/PM unless you use a 24 hour clock.

Biff
 
Top