Number Format Problem

  • Thread starter Thread starter Lana
  • Start date Start date
L

Lana

I have data in a cell and wish to convert to a "time
format" without having to re-enter data including colon.
Whenever I use number format and choose the time format I
need, it deletes the data and shows 0:00. Now if I re-
enter the time ex. 1710, it still shows 0:00. If I
enter "17.10" then it shows "2.24". At this rate I'd have
tonnes of data I'd have to re-enter ..... HELP!
 
Lana,

If you want to convert a set of values already existing, select all the
cells and run this macro

Sub Update()
Dim cell As Range

For Each cell In Selection
With cell
.Value = TimeSerial(.Value \ 100, _
.Value - (.Value \ 100) * 100, _
0)
End With
Next cell
End Sub

If you wan t to get it convered as you input it, this code will work for
column 8, H. Put it in the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 8 Then
With Target
.Value = TimeSerial(.Value \ 100, _
.Value - (.Value \ 100) * 100, _
0)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You need VBA fro that

http://www.cpearson.com/excel/DateTimeEntry.htm


I don't know why you would think you could enter 1710 in a cell and make
excel
change it to 17:00
1710 is 1710*24 = 4104 hours thus the result.. If you want excel treat
something as time
you need to use the time delimiter or VBA that will change it when you press
enter

You could also use a formula for entries already made, with 1710 in A1

=INT(A1/100)/24+MOD(A1,100)/1440

will return 17:10 formatted as time

you can use a help column and copy down, then copy and paste special as
values over the old
data
 
You have to enter time/date data in a format that Excel
will understand. One option would be to go ahead and key
in the time in your format of hhmm and then use a formula
in the next cell to convert:

=REPLACE(A1,3,,":")*1

Format the formula cell as time. Also, pre-format the
entry cells as text so when you key in "05:30", it appears
as 05:30, not 5:30.

HTH
Jason
Atlanta, GA
 
I have data in a cell and wish to convert to a "time
format" without having to re-enter data including colon.
Whenever I use number format and choose the time format I
need, it deletes the data and shows 0:00. Now if I re-
enter the time ex. 1710, it still shows 0:00. If I
enter "17.10" then it shows "2.24". At this rate I'd have
tonnes of data I'd have to re-enter ..... HELP!
Click to expand...

What sort of format is the time in now?

Be aware that using or changing a time format ONLY changes how the numbers are
displayed, and has no effect on how they are parsed when entered.

If the present cell entry is 1710 meaning 5 PM, then you will have to apply a
formula in order to convert that into a number which Excel can understand.
AFTER you do that, you can then use a time format of whatever you wish.

To convert a number of the type 1710 into an Excel time format, the following
formula will work:

=TIME(INT(A1/100),MOD(A1,100),0)


--ron
 
1710 in cell A1
enter in a cell next to it ..............=LEFT(A1,2)&":"&RIGHT(A1,2)
and extend down
you need to make sure that the original numbers all have 4 digits.

Good luck
Bill K
 
Back
Top