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:

    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


    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

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

    so that it becomes

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

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


  • Re: Time To String

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

    ActiveCell.NumberFormat = "@"

    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

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