# Time To String

• 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)

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.

• 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

• 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]

• 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)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!