Want to convert a number that looks like time to text.
Results 1 to 2 of 2

Thread: Want to convert a number that looks like time to text.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Want to convert a number that looks like time to text.

    I have pasted a value that looks like "3:30" into Excel from the web. This number is actually an elapsed time and stands for 3 minutes and 30 seconds of elapsed time. As a number i would like it to be 3.5 (i.e. "three and a half minutes"). Also have numbers like 12:30, etc. meaning 12 minutes and 30 seconds, or 12.5 minutes.

    In the formula bar, you see 3:30 AM when this cell is selected; Excel has interpreted this entry as time.
    If you format it as a number, you get 0.146, i.e. the fraction of a day.

    I cannot figure out a formula to convert this 3:30 (or 3:30AM, or .146) to the number 3.5. Any ideas?

    I thought maybe i could treat it as text, search for the ":", and then divide it into the parts "3" and "30" and convert that way, but using the SEARCH function on this value, ":" is not found, as Excel considers it to be a number, not text.

    Note, it is not practical to put an apostrophe before the value as it is a whole table of values (thousands) so that is not practical to do manually.

    A formula that could take that 3:30 and convert to 3.5, or 12:40 and convert to 12.67, etc would be appreciated. Thanks!

  2. #2
    New Member
    Join Date
    Mar 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Want to convert a number that looks like time to text.

    Ok Try this formula, assuming 3:30 is in A1:

    =A1*24
    And format as a NUMBER.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •