Time in Excel

  • Thread starter Thread starter John Oliver
  • Start date Start date
J

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?
 
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,

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.
Click to expand...

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.
Click to expand...

I had tried a couple of combinations of:

=AVERAGE(F4:F22)

But keep getting #NAME?
 
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,

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
Click to expand...

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!
 
A) "101" results in 1:01:00 I'd like it to result in 1:01
Click to expand...

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.
Click to expand...

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