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!

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

=3DA1/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

On Dec 14, 2:26=A0pm, Nelson B. <Nels...@discussions.microsoft.com>
wrote:
> How do you format a cell to convert a "seconds" input into minutes and
> seconds? =A0Whenever I try to select the format mm:ss, it converts into a=
 date
> with the month and year etc.
>
> Thanks!

Hi,

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

=A1/(60*60*24)

Format as [mm]:ss

Mike

"Nelson B." wrote:

> 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!

Nelson B.;586274 Wrote: 
> 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!

If you're entring these values at the keyboard, instead of entering
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:20


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=162310

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

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?

"Mike H" wrote:

> Hi,
> 
> I assume these 'seconds' are simply numbers so try this
> 
> =A1/(60*60*24)
> 
> Format as [mm]:ss
> 
> Mike
> 
> "Nelson B." wrote:
> 
> > 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!

Hi,

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


Mike



"Nelson B." wrote:

> 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?
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > I assume these 'seconds' are simply numbers so try this
> > 
> > =A1/(60*60*24)
> > 
> > Format as [mm]:ss
> > 
> > Mike
> > 
> > "Nelson B." wrote:
> > 
> > > 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!

Nelson B.;586305 Wrote: 
> 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?
> 
> "Mike H" wrote:
> 
> > Hi,
> >
> > I assume these 'seconds' are simply numbers so try this
> >
> > =A1/(60*60*24)
> >
> > Format as [mm]:ss
> >
> > Mike
> >
> > "Nelson B." wrote:
> >
> > > 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!

If my earlier suggestion doesn't suit then it's over to a macro. Right
click on the sheet's tab and select 'View code', and in the window that
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 the
range you want this to happen in. If it's a complicated, non-contiguous
range yu can quickly get the address string by starting to type into a
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 to
replace what's there now (D11:D26) while keeping the double quote
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 minutes
and seconds that can still be used in calculations.


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=162310

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]