convert numbers to time

[ditchy]

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]

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

[ditchy]

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