Prev: Filtering data based on historical values
Next: Copy and insert multipel rows after each row of data
From: da on 3 Jun 2010 18:33
hello
Is there any way to convert the following numbers to show as hrs:min without
manually insterting a colon?
e.g.15:45 18:40

1545 1840
2015 2300
2045 2105
1700 1815
1825 1840
1850 2000
1835 1900

From: Gary''s Student on 3 Jun 2010 19:09
Select the cells you want to convert and run this little macro:

Sub marine()
Dim hrs As Integer, mins As Integer
For Each r In Selection
v = r.Value
hrs = Left(v, 2)
mins = Right(v, 2)
r.Clear
r.NumberFormat = "hh:mm"
r.Value = TimeSerial(hrs, mins, 0)
Next
End Sub

--
Gary''s Student - gsnu201003


"da" wrote:

> hello
> Is there any way to convert the following numbers to show as hrs:min without
> manually insterting a colon?
> e.g.15:45 18:40
>
> 1545 1840
> 2015 2300
> 2045 2105
> 1700 1815
> 1825 1840
> 1850 2000
> 1835 1900
>
From: da on 4 Jun 2010 19:50
Thank you for your suggestion. I am not good at runnung macros.
But I found another formula that worked.

"Gary''s Student" wrote:

> Select the cells you want to convert and run this little macro:
>
> Sub marine()
> Dim hrs As Integer, mins As Integer
> For Each r In Selection
> v = r.Value
> hrs = Left(v, 2)
> mins = Right(v, 2)
> r.Clear
> r.NumberFormat = "hh:mm"
> r.Value = TimeSerial(hrs, mins, 0)
> Next
> End Sub
>
> --
> Gary''s Student - gsnu201003
>
>
> "da" wrote:
>
> > hello
> > Is there any way to convert the following numbers to show as hrs:min without
> > manually insterting a colon?
> > e.g.15:45 18:40
> >
> > 1545 1840
> > 2015 2300
> > 2045 2105
> > 1700 1815
> > 1825 1840
> > 1850 2000
> > 1835 1900
> >
 | 
Pages: 1
Prev: Filtering data based on historical values
Next: Copy and insert multipel rows after each row of data