If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert to hrs:min
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 |
#2
|
|||
|
|||
Convert to hrs:min
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 |
#3
|
|||
|
|||
Convert to hrs:min
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 |
Thread Tools | |
Display Modes | |
|
|