Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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). |
#2
|
|||
|
|||
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 , "XOXO" 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). |
#3
|
|||
|
|||
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 , "XOXO" 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). |
#4
|
|||
|
|||
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 , "XOXO" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert 00:03:54 to get 234 seconds? | Excel Worksheet Functions | |||
how to convert 357646 seconds into hh:mm:ss format? | Excel Worksheet Functions | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How do I format a cell for a custom part number? | Excel Discussion (Misc queries) | |||
How can I create custom format cells for Ohms(Ω)?? | Excel Worksheet Functions |