convert to time RRS feed

  • Question

  • I have the following macro that does a calculation. I’m trying to set it up for time. So an example of what I come up with for my Text18 bookmark is “12.45” and I want it to say “12 min 45sec”. Is there a way to due this?


    Sub InsertRatio1InBookmark()
    Dim num As Double
    Dim newText As String
    Dim myRange As Range
    On Error Resume Next
    If TextBox1 And TextBox2 = "0" Then
        newText = ""
    ptest1 = TextBox1
    ptest2 = TextBox2
        num = (CDbl(ptest1) / CDbl(ptest2)) * 60
        newText = CStr(Format(num, "##0.00"))
    End If

    FillBookmark "Text18", newText

    End Sub

    Friday, June 10, 2011 7:05 PM


All replies

  • Try this

        Debug.Print Hour(Time) & "hours " & Minute(Time) & "min " & Second(Time) & "sec"

    Kind Regards, Rich ...
    Friday, June 10, 2011 9:00 PM
  • Thanks for the reply Rich, but I don't understand your responce. For and example if newText = 12.45 and it fills the bookmark "Text18" as 12.45, how does what you wrote fit into the macro?
    Friday, June 10, 2011 9:58 PM
  • Maybe I misunderstood but I thought you were looking to take the current time and translate it into a bookmarked location in a document. Your other requirement is to format the time value into "##min ##sec" text.

    What I was showing you was how to take the current time's hour, minute, and seconds and place them into a string formatted as you require. The string in this case was a print string but the same concept would hold if you were transferring it to a bookmarked location in the document. 

    Sorry if I have misunderstood what you are trying to say and do.


    Kind Regards, Rich ...
    Saturday, June 11, 2011 1:39 AM
  • Please ignore this post. My Alert on this thread got turned off and I'm trying to reset it.
    Kind Regards, Rich ...
    Saturday, June 11, 2011 12:57 PM
  • Hi

    If you use this formula in your code it will give you what you are trying to do.

    newText = CStr(Int(num)) & " Mins " & CStr(Format(num -Int(num), "#0.00")) & "sec"

    However are you entering the information in TextBox1 & 2 as decimals or times.  Eg 12.5 or 12:30 (mm:ss format).  In the example you have given 12.45 is 12 min 27sec. With this data num =12.45 the formula above gives 12 min 45sec


    G North MMI
    Saturday, June 11, 2011 3:20 PM
  • This worked perfectly. Thank you for your help.


    • Marked as answer by fuzzhead58 Saturday, June 11, 2011 6:00 PM
    Saturday, June 11, 2011 6:00 PM