Formating seconds into mm:ss

[Nelson B.]

How do you format a cell to convert a "seconds" input into minutes and
seconds? Whenever I try to select the format mm:ss, it converts into a date
with the month and year etc.

Thanks!

 

[Pete_UK]

If you have seconds as an integer in cell A1, put this in B1:

=A1/60/60/24

and apply a custom format to cell B1 of:

[mm]:ss

Times are stored internally in Excel as fractions of a 24-hour day,
hence the need for those divisions.

Hope this helps.

Pete

 

[Mike H]

Hi,

I assume these 'seconds' are simply numbers so try this

=A1/(60*60*24)

Format as [mm]:ss

Mike

 

[p45cal]

How do you format a cell to convert a "seconds" input into minutes and
seconds? Whenever I try to select the format mm:ss, it converts into
date
with the month and year etc.

Thanks!

If you're entring these values at the keyboard, instead of enterin
seconds as 4400
enter as
0:0:4400
the cell can be formatted as [mm]:ss before or after this operation
The result in this case is
73:2

 

[Nelson B.]

Thanks for the help. This works, but I was hoping to be able to simply
change the format of a cell, so an input of 80 sec would show as 01:20. Is
that possible or does it require the use of a formula?

Hi,

I assume these 'seconds' are simply numbers so try this

=A1/(60*60*24)

Format as [mm]:ss

Mike

How do you format a cell to convert a "seconds" input into minutes and
seconds? Whenever I try to select the format mm:ss, it converts into a date
with the month and year etc.

Thanks!

 

[Mike H]

Hi,

I don't believe that is possible to do as a format.


Mike

Thanks for the help. This works, but I was hoping to be able to simply
change the format of a cell, so an input of 80 sec would show as 01:20. Is
that possible or does it require the use of a formula?

Hi,

I assume these 'seconds' are simply numbers so try this

=A1/(60*60*24)

Format as [mm]:ss

Mike

How do you format a cell to convert a "seconds" input into minutes and
seconds? Whenever I try to select the format mm:ss, it converts into a date
with the month and year etc.

Thanks!

 

[p45cal]

Thanks for the help. This works, but I was hoping to be able to simply
change the format of a cell, so an input of 80 sec would show as 01:20
Is
that possible or does it require the use of a formula?

Hi,

I assume these 'seconds' are simply numbers so try this

=A1/(60*60*24)

Format as [mm]:ss

Mike

How do you format a cell to convert a "seconds" input into minute and
seconds? Whenever I try to select the format mm:ss, it convert into a date
with the month and year etc.

Thanks!

If my earlier suggestion doesn't suit then it's over to a macro. Righ
click on the sheet's tab and select 'View code', and in the window tha
opens up, where the cursor is flashing, paste this:


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhand
Application.EnableEvents = False
Dim InputRng As Range, xxx
Set InputRng = Range("$D$11:$D$26")
Set xxx = Intersect(Target, InputRng)
If Not xxx Is Nothing Then
For Each cll In xxx.Cells
cll.Value = cll.Value / 60 / 60 / 24
cll.NumberFormat = "[mm]:ss"
Next cll
End If
errhand:
Application.EnableEvents = True
End Sub

-------------------

not forgetting to adjust the line starting 'Set InputRng =' to th
range you want this to happen in. If it's a complicated, non-contiguou
range yu can quickly get the address string by starting to type into
cell the formula
=sum(
and then selecting the various ranges while holding down the Ctrl key
You'll end up with a formula like
=SUM(G6:G9,I11:I16,K20:K25)
you just need to copy the 'G6:G9,I11:I16,K20:K25' bit into the code t
replace what's there now (D1126) while keeping the double quot
marks.

Now when you type the number of seconds (or paste one or more values
into the cells of that range they'll be converted to true Excel minute
and seconds that can still be used in calculations