Create a custom format to convert seconds to minutes.

X

XOXO

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).
 
J

JE McGimpsey

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

XOXO

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 said:
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.

XOXO said:
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).
Click to expand...
Click to expand...
 
J

JE McGimpsey

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
 
Top