Create a custom format to convert seconds to minutes.

Does any body know how to create a custom format to automatically convert
seconds into minutes. The seconds will be enter in a cell as seconds but the
formula needs to convert them in minutes (same cell).

Formats cannot change the values in cells.

You could enter the seconds in time format:

0:478.0

(the .0 is crucial, otherwise the entry will be interpreted as 0 hours,
478 minutes). Format as "[m]" if you only want to see minutes.

OTOH, assuming that you're talking about entering seconds as integers,
you can calculate the minutes in another cell as

B1: =TIME(0,0,A1)

or

B1: =A1/86400

Format B1 as "[m]". These return XL times.

or

B1: =MINUTES(A1/86400)

Which will return an integral number of minutes.

Note that all these will truncate the number of seconds to the lowest
minute, so A1=478 (7:58) will display as 7. You can use the ROUND()
function to compensate.

The only other way to do it in-cell is using an event macro. Post back
if you're interested.

In article <[email protected]>,
"XOXO" <[email protected]> wrote:

> Does any body know how to create a custom format to automatically convert
> seconds into minutes. The seconds will be enter in a cell as seconds but the
> formula needs to convert them in minutes (same cell).

Thanks JE, I did a custom format [mm] but I still need to enter 0:0:478 and
on the cell I get 7 which is good. But is there a way where I can enter the
478 and get 7 in return. Will the macro be able to do this.

Thank You.

"JE McGimpsey" wrote:

> Formats cannot change the values in cells.
>
> You could enter the seconds in time format:
>
> 0:478.0
>
> (the .0 is crucial, otherwise the entry will be interpreted as 0 hours,
> 478 minutes). Format as "[m]" if you only want to see minutes.
>
> OTOH, assuming that you're talking about entering seconds as integers,
> you can calculate the minutes in another cell as
>
> B1: =TIME(0,0,A1)
>
> or
>
> B1: =A1/86400
>
> Format B1 as "[m]". These return XL times.
>
> or
>
> B1: =MINUTES(A1/86400)
>
> Which will return an integral number of minutes.
>
> Note that all these will truncate the number of seconds to the lowest
> minute, so A1=478 (7:58) will display as 7. You can use the ROUND()
> function to compensate.
>
> The only other way to do it in-cell is using an event macro. Post back
> if you're interested.
>
> In article <[email protected]>,
> "XOXO" <[email protected]> wrote:
>
> > Does any body know how to create a custom format to automatically convert
> > seconds into minutes. The seconds will be enter in a cell as seconds but the
> > formula needs to convert them in minutes (same cell).
>

One way:

put this in your worksheet code module (right-click the worksheet tab
and choose "View Code"):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Not Intersect(.Cells, Range("A1")) Is Nothing Then
Application.EnableEvents = False
.Value = .Value / 86400
.NumberFormat = "[m]"
Application.EnableEvents = True
End If
End With
End Sub

If you're not familiar with macros, see David McRitchie's "Getting
Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article <[email protected]>,
"XOXO" <[email protected]> wrote:

> Thanks JE, I did a custom format [mm] but I still need to enter 0:0:478 and
> on the cell I get 7 which is good. But is there a way where I can enter the
> 478 and get 7 in return. Will the macro be able to do this.