Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert numbers to time
Hello I need some help creating a macro that will convert number pad
time to 0:00:00, What the macro needs to do is check the Column or Cells to see what format is used. If format is "0.00" or "general", convert to time with this formula, =(INT(A1)+MOD(A1,1)*100/60)/1440 (or an equivalent formula) and then format to 0:00:00. If the Column or Cells formated as 0:00:00 leave as is.Is this possible to achieve?, there is a lot of data I would like to convert in multiple columns and cells thank you Ditchy, Ballarat |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert numbers to time
Select the cells you want to convert and try running this macro.
Option Explicit Sub testme02() Dim myCell As Range Dim myRng As Range Set myRng = Selection For Each myCell In myRng.Cells With myCell If .NumberFormat = "General" _ Or .NumberFormat = "0.00" Then .Value _ = (Int(.Value) + ((.Value - Int(.Value)) * 100 / 60)) / 1440 .NumberFormat = "hh:mm:ss" End If End With Next myCell End Sub Remember to save before you run it. If it's wrong, you'll want to close without saving. ditchy wrote: Hello I need some help creating a macro that will convert number pad time to 0:00:00, What the macro needs to do is check the Column or Cells to see what format is used. If format is "0.00" or "general", convert to time with this formula, =(INT(A1)+MOD(A1,1)*100/60)/1440 (or an equivalent formula) and then format to 0:00:00. If the Column or Cells formated as 0:00:00 leave as is.Is this possible to achieve?, there is a lot of data I would like to convert in multiple columns and cells thank you Ditchy, Ballarat -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
convert numbers to time
Thank you Dave
this is perfect much appreciated, regards Ditchy Dave Peterson wrote: Select the cells you want to convert and try running this macro. Option Explicit Sub testme02() Dim myCell As Range Dim myRng As Range Set myRng = Selection For Each myCell In myRng.Cells With myCell If .NumberFormat = "General" _ Or .NumberFormat = "0.00" Then .Value _ = (Int(.Value) + ((.Value - Int(.Value)) * 100 / 60)) / 1440 .NumberFormat = "hh:mm:ss" End If End With Next myCell End Sub Remember to save before you run it. If it's wrong, you'll want to close without saving. ditchy wrote: Hello I need some help creating a macro that will convert number pad time to 0:00:00, What the macro needs to do is check the Column or Cells to see what format is used. If format is "0.00" or "general", convert to time with this formula, =(INT(A1)+MOD(A1,1)*100/60)/1440 (or an equivalent formula) and then format to 0:00:00. If the Column or Cells formated as 0:00:00 leave as is.Is this possible to achieve?, there is a lot of data I would like to convert in multiple columns and cells thank you Ditchy, Ballarat -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to convert Time String to Time | Excel Worksheet Functions | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
how do I convert a date and time column to a time column | Excel Worksheet Functions | |||
Convert text to numbers | Excel Discussion (Misc queries) | |||
How to convert Numbers to text | Excel Worksheet Functions |