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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.