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

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

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