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 > >