Time in Excel

[John Oliver]

I have a legacy spreadsheet that deals with times. We have values like
min:sec and use 12:00:00 AM in the cell for 00:00 To input a time, I
need to edit the cell. Is there a way to go to a cell and type "656"
and get 06:56?

Also, I'd like a way to average several cells containing times as above.
What function would I use for that?

 

[Bob Phillips]

John,

To do the first, that is modify the input to time, requires VBA. A worksheet
event code like

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If Target.Count = 1 Then
Target.Value = TimeSerial(Target \ 100, Target - ((Target \ 100)
* 100), 0)
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

which will modify cells A1:A10.

To average time, just use the average function, and make sure the cell is
formatted as time, and you don't put it in the range being changed.

 

[John Oliver]

John,

To do the first, that is modify the input to time, requires VBA. A worksheet
event code like

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If Target.Count = 1 Then
Target.Value = TimeSerial(Target \ 100, Target - ((Target \ 100)
* 100), 0)
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

which will modify cells A1:A10.

Thanks. Being an almost complete Excel newb, though, I gotta ask... how
do I put in that VBA code?

To average time, just use the average function, and make sure the cell is
formatted as time, and you don't put it in the range being changed.

I had tried a couple of combinations of:

=AVERAGE(F4:F22)

But keep getting #NAME?

 

[Bob Phillips]

John,

To input the code
- on the sheet you want it to apply to
- right-click the sheet tab
- on this menu, select View Code
- you should now be in the worksheet code module, so in the code pane just
copy the code I gave you
- test it by putting a value in A1

Re AVERAGE, the error you are getting sounds like the function is wrongly
entered. For instance, if you put =AVRAGE(A1:A22) you would get that error.
Otherwise I am stumped.

 

[John Oliver]

John,

To input the code
- on the sheet you want it to apply to
- right-click the sheet tab
- on this menu, select View Code
- you should now be in the worksheet code module, so in the code pane just
copy the code I gave you
- test it by putting a value in A1

Ahh!

Two other questions...

A) "101" results in 1:01:00 I'd like it to result in 1:01

B) I want this to work with more than one range of cells. I'm not sure
how to properly specify more than one range.

Thanks so much!

 

[John Oliver]

A) "101" results in 1:01:00 I'd like it to result in 1:01

To be more precise, minutes and seconds. Unless I type, say, 12345,
which would be 1:23:45

B) I want this to work with more than one range of cells. I'm not sure
how to properly specify more than one range.

I stumbled on how to do this... I used Range("E4:E27", "F4:F27")