Time To String

telymanbws · Mar 17th 2007

I am converting minutes to [H]:mm (thank you DaveR (UK) for this part)

The formula bar shows:
1/1/1900 11:51:00

The cell contains:
35:51

Is there a way to convert the 35:51 to a string and not time?
(changing the cell format to general after the 35:51 is placed gives "1.49375" which is the time in its raw format)

Code

Sub test_time_2_String()


Dim a As Variant
Dim b As Integer
Dim c As Variant
Dim d As String, e As String, F As String




   Cells(10, 21).Activate   ' Arbitrary cell


' Break it down to its smallest parts in order to watch the progression


    a = 2150.533333                     ' this is 2150.533333 minutes
    b = a                               ' b = 2150 (b = integer)
    c = b / 60                          ' c = 35.8333333
    d = Int(c)                          ' d = "35" [hours]
    e = Int(c - Val(d)) * 60            ' e = .833333 * 60 = "51" [minutes]
    F = d & ":" & Format(e)             ' use format so "35:51" isn't "35: 51"
    ActiveCell.NumberFormat = "[h]:mm"
    ActiveCell = F                      ' !! Need to convert this to string !!
end sub

Display More

I Dimmed d, e, & F as strings yet it still formats the cell as time as soon as it's pasted.

The ActiveCell.NumberFormat = "[h]:mm" line is there so a least I have 35:51 instead of 35:51:00. Neither General or Text formats worked.

Robert B · Mar 17th 2007

Re: Time To String

Hi

generally, if an ' is placed before a numeric value the entry is rendered as a text string. Maybe this could be applied to your line of code

Code

F = d & ":" & Format(e)

so that it becomes

Code

F = "'" & d & ":" & Format(e)

although I am not sure how the next line of your code would react.

Robert

rbrhodes · Mar 17th 2007

Re: Time To String

Robert B is correct. Putting a ' before text displays as text. This will also work:

ActiveCell.NumberFormat = "@"

[vba]
Sub test_time_2_String()

Dim a As Double 'to keep .#####
Dim b As Integer
Dim c As Double 'as above
Dim d As Integer
Dim e As Integer
Dim F As String

'Arbitrary cell
Cells(10, 21).Activate

'Break it down to its smallest parts in order
' to watch the progression

'this is 2150.533333 minutes
a = 2150.533333
'b = 2150 (b = integer)
b = Int(a)
'c = 35.8333333
c = b / 60
'd = 35 [hours] USE INT or is rounded up to 36
d = Int(c)
'e = .833333 * 60 = IS 50 Not 51 [minutes]
e = (c - d) * 60
'NO NEED: use format so "35:51" isn't "35: 51" you 'must have had ": " (a space included)
F = d & ":" & e
'use with/end with instead of Activecell 3 times
With ActiveCell
'format cell as text
.NumberFormat = "@"
'it is a string! !! Need to convert this to string !!
.Value = F
'default aligned left as text.
' To look like number: align right
.HorizontalAlignment = xlRight
End With

End Sub
[/vba]

telymanbws · Mar 20th 2007

Re: Time To String

That did it! Thanks to all!

BTW: the space (35: 51") was because I was using VAL instead of format. VAL puts a space for the sign (+ or -) whether it's used or not. A positive number assumes the sign but still leaves a space. I thought it was an extra space after the : (": " vice ":") but it wasn't. The remark was left over as a reminder to myself not to use VAL when using a string in a formula that had to retain its stringdom. (My new word for the day)

Time To String

Participate now!

Share