PDA

View Full Version : Solved: Changing format from numbers to time



lucky245
09-01-2006, 01:24 AM
I have a column in an excel spreadsheet (over 500 rows) in which the user has input the time as 500 meaning 05:00 and 1350 meaning 13:50. Being more knowledgable about access than excel I need help to move down the column cell by cell and add ":" 2 char from the right. Hopefully this will then allow me to use the format command and the time will turn out correctly. The reason for this is I need to extract one time from another.

I appreciate you could probably write code to format it directly into time and that would also be great but the method asked for would allow me to manipulate strings in the future as well.

Thanks: pray2:

Bob Phillips
09-01-2006, 02:47 AM
Don't do it that way, convert to time using

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

and then format the new column as time

mdmackillop
09-01-2006, 02:49 AM
... but if you must, try

Option Explicit
Dim cel As Range
Sub tim()
For Each cel In Selection
If cel <> "" Then
cel.Value = Int(cel / 100) & ":" & Right(cel, 2)
End If
Next
End Sub

Bob Phillips
09-01-2006, 02:51 AM
Malcolm



cel.Value = cel \ 100 & ":" & Right(cel, 2)


:)

lucky245
09-01-2006, 03:06 AM
Excellent solutions thankyou

Aussiebear
09-01-2006, 04:26 AM
I had found the following somewhere out there in the www. but am having trouble getting it to function


Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal As Integer

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100") Is Nothing Then Exit Sub

With Target

vVal = Format(.Value, "0000")
If ISNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents= False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End IF
End With
Application.EnableEvents = True
End Sub


When I enter a value, say 07.00 for 7am, it throws up a wierd result which includes a date and the wrong time.

So this thread is most timely

Ted

mdmackillop
09-01-2006, 05:23 AM
Hi Ted,
If vVal is Integer then vVal = Format(.Value, "0000") remains as 700, ie 3 characters long. Dim vVal as String instead.

mdmackillop
09-01-2006, 05:24 AM
Malcolm



cel.Value = cel \ 100 & ":" & Right(cel, 2)


:)
Interesting Bob. Where does the decimal part go?

Aussiebear
09-01-2006, 06:11 AM
Works well Malcolm. If I wanted to have the result as 07:30 am for a different range do i need to repeat the code, change the range location and then the time format to "[h]:mm AM/PM"?

BTW Can I call the WorkSheet_Change as WorkSheet_Change 2 to differentiate between the two sets of code?

Bob Phillips
09-01-2006, 07:24 AM
No, you test the two ranges separately in the same procedure.

Bob Phillips
09-01-2006, 07:25 AM
Interesting Bob. Where does the decimal part go?
What decimal part Malcolm?

mdmackillop
09-01-2006, 08:45 AM
I never noticed the \ operator. I was thinking of /. :blush Don't know that I've ever used \ but I recall reading about it.

Cyberdude
09-01-2006, 12:50 PM
I too remember reading about the operator "\", but I can't find it now. Can you refresh my memory about what it does please? Is that the "exclusive OR"?? :read:

Bob Phillips
09-01-2006, 04:19 PM
I too remember reading about the operator "\", but I can't find it now. Can you refresh my memory about what it does please? Is that the "exclusive OR"?? :read:

No, it is integer divide. It is equivalent to



Int(a / b)

Cyberdude
09-02-2006, 11:37 AM
Interesting. So the Int is applied to the quotient, not to the divisor and dividend. Gotta play with that. Thanks!