Entering Seconds but displaying minutes & seconds

D

DaveWHAC

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

Dave Peterson

You could use a helper cell with a formula:

=A1/24/60/60

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

DaveWHAC

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
 
D

Dave Peterson

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.
 
D

DaveWHAC

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
 
D

Dave Peterson

It sounds like events are disabled.

Try closing excel and restarting excel, then reopen your workbook. Make sure
you answer yes to enable macros (and make sure tools|macro|security level is set
for medium or low before you open that workbook).

ps. if you get March 26, 1904, this means that you are using the 1904 date
system. This won't affect the macro, but it could cause trouble if you
copy|paste dates from one workbook to another workbook that doesn't use 1904
date system.

Most pcs use 1900 and most macs use 1904--but it's your choice.

Be aware that if you do change this base date, then all your existing dates will
be off by 4 years and one day.


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

DaveWHAC

Hi Dave,

That's it sorted now, and it was far from simple. I wouldn't have ever got
to that point with it!!

Don't know if you are meant to reply with thanks as I've not used this
before, but thanks for all your efforts.

DaveWHAC
 
D

Dave Peterson

Glad it worked ok.

And "thank you" replies are always welcome.


Hi Dave,

That's it sorted now, and it was far from simple. I wouldn't have ever got
to that point with it!!

Don't know if you are meant to reply with thanks as I've not used this
before, but thanks for all your efforts.

DaveWHAC
Click to expand...
 
Top