Announcement

Collapse
No announcement yet.

Converting Custom Format to Text Then Apply a Value After Calculation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Converting Custom Format to Text Then Apply a Value After Calculation



    Hi OzGrid,

    Although it may be little bit complicated on the topic title, it's something that I have been working on it for days. Excel can calculate negative time span values under certain circumstances if I am correct: 1904 system or converting to a text. At the beginning, I fully rejected to approach these particular solutions. Instead, I built up a formula to break the time into pieces by twos with LEFT, MID, RIGHT as if they are numbers separated with colon (like a modem's mac address) then applied a value. It worked! Subtracted one from another... Negative value appeared as I expected (I have to tell you that I also used a customer format 00\:00\:00.

    However, it does not work when It comes to cells contain calculated span time values.

    A2 contains this: 00:20:15 this comes from with INT formula and subtraction E2 and F2 both contains date with time.
    A3 contains this: 00:10:15 this comes from another cell G2 (a span time; hours, minutes, seconds) plus TIME(0,3,30)

    I want A4 to display subtraction A3-A2... Because neither A2 nor A3 is text. They are [hh]:mm:ss

    I'd like to apply a trick... In A4, how can I convert them to text first, then apply a subtraction calculation and display the result as a value like they are numbers in the same formula without helper or VBA.

    Your help will highly be appreciated on this mixed problem. Thanks in advance.
    "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke

  • #2
    Hi,

    A tiny sample file with a couple of specific examples of your expected results ... would help ...
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      perhab like this
      A4=TIMEVALUE(A3)-TIMEVALUE(A2)
      if you have format text not : ex / you can join timevalue + substitute

      Comment


      • #4
        Originally posted by Carim View Post
        Hi,

        A tiny sample file with a couple of specific examples of your expected results ... would help ...
        Hello Carim,

        Sorry for delay cause I was in rush. Just added sample file you requested which all explanations are inside the file.

        Thanks in advance.
        Attached Files
        "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
        Sir Arthur C. Clarke

        Comment


        • #5
          H
          Originally posted by graha_karya View Post
          perhab like this
          A4=TIMEVALUE(A3)-TIMEVALUE(A2)
          if you have format text not : ex / you can join timevalue + substitute
          Hi Graha,

          I tried a very similar solution to yours, probably it's almost close to what I want to see but it's a little bit complicated in my case. Because I don't want to use 1904 system as well as displaying time span as text for some reasons. Regarding your sample, results are in A3 and A2 both are calculated separetly which their values come from another cells. And I am trying to make another calculation in another cell over A3 and A2. Though I'll try your instance again with another approach. Thank you much.
          "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
          Sir Arthur C. Clarke

          Comment


          • #6
            Hello,

            Thanks for your file ...

            A simple Text function should help you out ...

            see your file with a tiny modification ...

            Hope this will help
            Attached Files
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Thank you very much Carim. It works like charm as always. Marked as solved.
              "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
              Sir Arthur C. Clarke

              Comment


              • #8


                Glad you could sort this out ...

                Thanks a lot ... for your Thanks ..AND for the Like ...
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment

                Working...
                X