none
Time formating question RRS feed

  • Question

  • I have the following macro with help from G North that sets my formating correctly but I have a follow up question. When I plug in my numbers and it loads the info into my form I could get the following answer: 16 mins 74 sec. Is there a way to correct this to say the following: 17 mins 14 sec?

    Dim num As Double
    Dim num1 As Double
    Dim newText As String
    Dim myRange As Range
    On Error Resume Next

    If TextBox1 = "" Then
        newText = ""
    ElseIf TextBox2 = "" Then
        newText = ""
    Else
    ptest1 = TextBox1
    ptest2 = TextBox2
        num1 = (CDbl(ptest2) / 60)
        num = (CDbl(ptest1) / num1)
        newText = CStr(Int(num)) & " mins " & CStr(Format(num - Int(num), "#.00")) & " sec"
    End If
    FillBookmark "Text18", newText

    Saturday, June 11, 2011 9:50 PM

Answers

  • Tony,

    This worked. Sorry about the other post. I was busy writing it up before I saw that you had replyed to the other post.

    Thank you for your help.

    Larry

    • Marked as answer by fuzzhead58 Sunday, June 12, 2011 6:12 PM
    Sunday, June 12, 2011 6:12 PM

All replies

  • I'm not quite sure why you've started a new question for this, but ..
     
    1. To save me the trouble of reverse engineering your code, what are you entering in your form that might produce this result?
     
    2. The formatting you show puts (or leaves) a decimal point before the seconds. If you are happy with that you could do things more simply with ..
     
        CStr(Format(num, "#0 Min .00 Sec"))
     
     .. or, if not, I think this will remove it ..
     
        CStr(Format(num * 100, "#0 Min [.]00 Sec"))
     

    Enjoy,
    Tony
    www.WordArticles.com
    Sunday, June 12, 2011 6:58 AM
  • Sorry, I left a bracketed decimal point in by mistake. The last line should have read ..
     
        CStr(Format(num * 100, "#0 Min 00 Sec"))
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Sunday, June 12, 2011 7:01 AM
  • Thanks for answering Tony. I guess I didn’t explain what I was trying to do correctly. When I input my numbers in and run the calculation I could end up with a number like 19.63. Which then is displayed in my form as 19 mins 63 sec. What I am trying to do is if the number to the right of the period is greater than 60 it would subtract 60 from that number and add 1 to the number left of the period. So in my example above 19.63 would become 20.03 and displayed in my form as 20 mins 03 sec. I hope this makes more sense.

    Sunday, June 12, 2011 3:19 PM
  • I understood what you asked. I wanted to know what was being input to your calculation to see if there might be an easier way as it seemed an odd result to be getting to begin with. Anyway, what you need to do is something along the lines of ...
     
        num2 =  num + 0.4
        If Int(num2) > Int(num) then num = num2
        newText = CStr(Format(num * 100, "#0 Min 00 Sec"))
     
    In theory you can do it all in one statement, but floating point arithmetic is funny, and it might not give the right result for boundary values, and it's best separated out.
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Sunday, June 12, 2011 5:32 PM
  • Tony,

    I tried this and it worked if my number to the right of the period is greater than 60 but if it's less than 60 it changes to a wrong number.

     

        num = (CDbl(ptest1) / CDbl(ptest2)) * 60

        If num > "#.6" Then

            num = (num - 0.6) + 1

        End If

        newText = CStr(Format(num * 100, "#0 Min 00 Sec"))

     

    Example A

    ptest1 = 450

    ptest2 = 1375

    num = (450 / 1375)*60 = 19.64

      

    using the fomula above I get this:

    newText = 20 Mins 04 sec

     

     

    Example B

    ptest1 = 480

    ptest2 = 1230

     

    num = (480 / 1230)*60 = 23.41

     

    using the fomula above I should get this:

    newText = 23 Mins 41 sec

     

    What I end up with is this:

    newText = 23 Mins 81 sec

     

    I don’t know where the 81 sec is coming from.

     

     

    Sunday, June 12, 2011 5:39 PM
  • Tony,

    This worked. Sorry about the other post. I was busy writing it up before I saw that you had replyed to the other post.

    Thank you for your help.

    Larry

    • Marked as answer by fuzzhead58 Sunday, June 12, 2011 6:12 PM
    Sunday, June 12, 2011 6:12 PM