Hi Dave,

Sorry to be a pest but I'm still struggling with this. I've cut and pasted
what you typed into the view code, made my range c:h as those are the columns
I wanted to affect, but typing in 85 in cell c5 (top left of the range)
generates 00:00 and the formula at the top reads 26/3/1904 00:00:00

Many thanks

DaveWHAC

"Dave Peterson" wrote:

You could use a worksheet event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler:

If IsNumeric(.Value) Then
If Int(.Value) = .Value Then
Application.EnableEvents = False
.Value = .Value / 24 / 60 / 60
.NumberFormat = "mm:ss"
End If
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

Change the range that needs the conversion. I used A:A.

DaveWHAC wrote:

Hi Dave,

Thanks for that, but I was trying to avoid using other cells to reference
back to. Ideally I want to type in 85 in the cell and have that same cell
format it as 1:25

Any other suggestions ?

DaveWHAC

"Dave Peterson" wrote:

You could use a helper cell with a formula:

=A1/24/60/60

And format it as mm:ss (or your favorite format)



DaveWHAC wrote:

I'm trying to set up a spreadsheet so that I can enter seconds into a variety
of cells e.g. 85 but I want it to be displayed as 1:25 when looking at the
spreadsheet.

Can anyone help ?

--

Dave Peterson


--

Dave Peterson