formatting cells to show time format

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

Kevin

Hi
I wonder if you can help me with the following. I use
Office 97 Professional, and Excel 97 to do the following.
I have created a spreadsheet that will be used to
calculate times & results for Car Rallies. I have sorted
out the formatting of the cells so they show hours,
minutes, seconds, and down to thousands of a second,
using this time format hh:mm:ss.000. I have done the
formulas to work out the elapsed times and then total
times, by taking the finish time from the start time etc,
then adding elapsed times together to get total times.
The thing I am trying to sort out is wheather it is
possible to format the cells that contain the start and
finish data to automatically show the separators instead
of have to type in the separators. e.g inputting
125535555 off the numeric key board, into the selected
cell, would show 12:55:35.555 when pushing enter.
The reason I would like to do the above is that the
persons using the above spreadsheet most likely will not
be very keyboard proficient.If we can create the
spreadsheet in such away that they only have to select
cell and use the numeric keys instead of having to
physically type in the separators this will hopefully
eliminate some of the possible errors.

regards

Kevin
 
Here is one that does 14:00 from 1400. You MUST enter all 4, not 2 or 3.
Copy the whole thing to a new module by itself. Modify to suit. BTW. I'm an
ex SCCA Formula Ford driver.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Ron Rosenfeld
<[email protected]>
Dim InputRange As Range
Dim Temp As Variant
Dim i As Integer
Dim Separators(2) As String

Separators(0) = ";"
Separators(1) = ","
Separators(2) = "."


Application.EnableEvents = False

Set InputRange = [A1:A100] 'or whatever
If Intersect(Target, InputRange) Is Nothing Then GoTo Bye
If Target.Value = 0 Then GoTo Bye
Temp = Target.Value

'If entry is an integer, then make it a time

On Error GoTo ConvertString
If Int(Temp) = Temp Then
Temp = (Int(Temp / 100) + (Temp / 100 - Int(Temp / 100)) * 100 / 60) /
24
GoTo Last
ElseIf Temp < 1 Then 'probably a time
GoTo Last
End If

If Temp > 15000 Then 'probably a date/time string
Temp = CDate(Temp - Int(Temp))
GoTo Last
End If

ConvertString: 'replace separator with colon unless the string is a
date
On Error GoTo 0
For i = 0 To 2
Temp = Replace(Temp, Separators(i), ":")
Next i


Last:
Target.Value = Temp
Target.NumberFormat = "hh:mm"
Bye: Application.EnableEvents = True
End Sub
 
And, here's one that I did that might be easier. Modify to suit & pre-format

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 Or Target.Column = 5 Then
On Error GoTo endit
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
'Target = Format(Left(Target, 2) & ":" & Right(Target, 2), "hh:mm")
End If
endit:
Application.EnableEvents = True
End Sub
 
Donald
Thanks for your response. It appears what I want can be
done however as I am very much a learner I unfortunatly
do not understand what you have told me. Is this a
formula or a macro or what. Thanks
 
Back
Top