Reformat existing 4 digit number as hh:mm?
 

I have a column of 3 and 4 digit numbers. I would like to insert a colon :
to the immediate left of the 2 numbers on the far right of the cell. Is
there a way to do this easily?

Reformat existing 4 digit number as hh:mm?
 

Carol
if i read you correctly you need timeserials

add a temporary column..

if the last 2 digits are seconds then
with formula =TIMEVALUE(TEXT(B1,"00\:00\:00"))
if they are minutes then
with formula =TIMEVALUE(TEXT(B1,"00\:00"))

dragdown.. then copy /pastespecial values..





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Carol Wolfe wrote :

I have a column of 3 and 4 digit numbers. I would like to insert a
colon : to the immediate left of the 2 numbers on the far right of
the cell. Is there a way to do this easily?

Reformat existing 4 digit number as hh:mm?
 

That didn't seem to work, here's a sample of what happened:

840 0.361111111
1213


I copied your formula into the cell in my temporary column

"keepITcool" wrote:


Carol
if i read you correctly you need timeserials

add a temporary column..

if the last 2 digits are seconds then
with formula =TIMEVALUE(TEXT(B1,"00\:00\:00"))
if they are minutes then
with formula =TIMEVALUE(TEXT(B1,"00\:00"))

dragdown.. then copy /pastespecial values..





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Carol Wolfe wrote :

I have a column of 3 and 4 digit numbers. I would like to insert a
colon : to the immediate left of the 2 numbers on the far right of
the cell. Is there a way to do this easily?

Reformat existing 4 digit number as hh:mm?
 

..3611 is the correct time serial..
you only need to format the new column to display the number as time.








--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Carol Wolfe wrote :

That didn't seem to work, here's a sample of what happened:

840 0.361111111
1213


I copied your formula into the cell in my temporary column

"keepITcool" wrote:


Carol
if i read you correctly you need timeserials

add a temporary column..

if the last 2 digits are seconds then
with formula =TIMEVALUE(TEXT(B1,"00\:00\:00"))
if they are minutes then
with formula =TIMEVALUE(TEXT(B1,"00\:00"))

dragdown.. then copy /pastespecial values..





--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Carol Wolfe wrote :

I have a column of 3 and 4 digit numbers. I would like to insert
a colon : to the immediate left of the 2 numbers on the far
right of the cell. Is there a way to do this easily?

Reformat existing 4 digit number as hh:mm?
 

If you don't want it to be an actual time that you can manipulate
mathematically then you can just use text functions

=IF(LEN(A2)=3, LEFT(A2, 1) & ":" & RIGHT(A2,2), LEFT(A2,2) & ":" &
RIGHT(A2,2))
Where the number is in Cell A2.

Cools function (without having tried it) is giving you the time, but it
needs to be re-foramtted. Select the cell with the formula and change the
format from general to Time... It should then look correct...

HTH


"Carol Wolfe" wrote:

I have a column of 3 and 4 digit numbers. I would like to insert a colon :
to the immediate left of the 2 numbers on the far right of the cell. Is
there a way to do this easily?

Reformat existing 4 digit number as hh:mm?
 

spreadsheet

if C6 is your number, then in another cell
=SUBSTITUTE(C6,RIGHT(C6,2),":" & RIGHT(C6,2))

VBA

Sub Adjust()
Dim cell As Range
Dim text As String
For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants)
text = cell.Value
cell.Value = Replace(text, Right(text, 2), ":" & Right(text, 2))
Next
End Sub

"Carol Wolfe" wrote:

I have a column of 3 and 4 digit numbers. I would like to insert a colon :
to the immediate left of the 2 numbers on the far right of the cell. Is
there a way to do this easily?