Announcement

Collapse
No announcement yet.

Time format conversation from decimal numbers

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

  • Time format conversation from decimal numbers



    Hello Ozgrid members,

    This is how the minute and second look like in A1: 35.02
    I see that it's formatted as decimal number (decimal places 2 as negative). I want to change it to seconds only in next cell. Normally, desired result should look like this: 1277. I couldn't handle it any help would be great. Thank you!
    "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke

  • #2
    Is that supposed to be 35 minutes and 2 seconds? 35 minutes is equal to 2100 seconds which is higher than your desired outcome?
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3
      Code:
      =IF(ISERROR(FIND(".",A1,1)),A1 * 60,LEFT(A1,FIND(".",A1,1)-1) * 60 + MID(A1,FIND(".",A1,1)+1,3))
      this is how I did it. (I'm sure there are many other ways to do it!)

      check for the decimal, (ISERROR(FIND(".",A1,1)))

      If no seconds, multiply A1 by 60
      if seconds exist, find the decimal, multiply left of decimal by 60 and add right of decimal

      P.S.
      36 mins and 17 secs = 1277 secs

      Comment


      • #4
        Originally posted by NBVC View Post
        Is that supposed to be 35 minutes and 2 seconds? 35 minutes is equal to 2100 seconds which is higher than your desired outcome?
        Hello NBVC it's nice to hear of you again. Yes you are right. Faster you write, inaccurate results you get.

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

        Comment


        • #5
          Originally posted by rabsofty View Post
          [Code]

          P.S.
          36 mins and 17 secs = 1277 secs
          How? I think you got your digits re-arranged

          36 minutes * 60 sec/min = 2160 seconds
          + 17 seconds = 2177 seconds.

          Where there is a will there are many ways. Finding one that works for you is the challenge!

          MS Excel MVP 2010-2016

          Comment


          • #6
            Originally posted by onexc View Post

            Hello NBVC it's nice to hear of you again. Yes you are right. Faster you write, inaccurate results you get.
            Then perhaps...

            =INT(A1)*60+MOD(A1,1)*100

            Where there is a will there are many ways. Finding one that works for you is the challenge!

            MS Excel MVP 2010-2016

            Comment


            • #7
              Originally posted by rabsofty View Post
              Code:
              =IF(ISERROR(FIND(".",A1,1)),A1 * 60,LEFT(A1,FIND(".",A1,1)-1) * 60 + MID(A1,FIND(".",A1,1)+1,3))
              this is how I did it. (I'm sure there are many other ways to do it!)

              check for the decimal, (ISERROR(FIND(".",A1,1)))

              If no seconds, multiply A1 by 60
              if seconds exist, find the decimal, multiply left of decimal by 60 and add right of decimal

              P.S.
              36 mins and 17 secs = 1277 secs
              This one works as expected rabsofty; 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


              • #8


                Originally posted by NBVC View Post

                Then perhaps...

                =INT(A1)*60+MOD(A1,1)*100
                Thank you so much NBVC fantastic!
                "I donít pretend we have all the answers. But the questions are certainly worth thinking about."
                Sir Arthur C. Clarke

                Comment

                Working...
                X