antiquated excel 2003

[krhorner]

3 columns A B C
18:00 19:05 65 (Minutes)

Each column is military time and "C" is the total time elapsed in minutes.

Is there there a way to automatically enter the colon and still have excel recognize the entry as time? Currently I format columns A & B as
Number->Custom->h:mm and the Column C as Number -> Custom->
[mm]. I have to manually enter the colon instead of easily typing 1800 or 1905.

If I change the formats in the first two columns to 0":"00, the third column does not recognize the first two columns as time.

I am forced to use Excel 2003 for the compilation of these stats.

 

[Andrew Poulsom]

Maybe this will help you:

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

 

[krhorner]

Thanks for the quick response, but I am afraid it was written way above my head.

 

[Brian from Maui]

You can Custom Format the cells to

00\:00

Caveat is you'll need to convert the text times to true time values when computing

=TEXT(A1,"00\:00")+0

 

[Brian from Maui]

Or right click and paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    If Len(Target) = 3 Then .Value = Left(.Value, 1) & ":" & Right(.Value, 2)
    If Len(Target) = 4 Then .Value = Left(.Value, 2) & ":" & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

Where timecells is a named range that houses times.

 

[andrewkard]

Hello, you can type in other column, and in A set a formula:
=CONCATENATE(LEFT(D1,2),":",RIGHT(D1,2))