+ Reply to Thread
Results 1 to 9 of 9

Convert Text to Time (not as simple as it sounds)

  1. #1
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    72

    Convert Text to Time (not as simple as it sounds)

    I've googled converting text to time, and for simple bits I have answers.. what I'm struggling to find is if it's possible to convert something like 8:00am-4:30pm (in one cell) to a Start and End time (two separate cells)



    I'm working with something similar to this sample data...

    Please Login or Register  to view this content.

    Converted start and end times would go into a new sheet with the data underneath each row (as in, 2 rows inserted under each analyst, start time, and then end time.)
    Let me know if I can provide anything else here.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Convert Text to Time (not as simple as it sounds)

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    72

    Re: Convert Text to Time (not as simple as it sounds)

    Of course, my apologies, I think that's it attached.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Convert Text to Time (not as simple as it sounds)

    In the meantime, a PowerQuery solution.

    M Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,958

    Re: Convert Text to Time (not as simple as it sounds)

    Using Power Query/Get and Transform, you can convert in several steps. Here is a video tutorial on how to do that.

    https://www.youtube.com/watch?v=JMOnr3DOqyk

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    2016/365
    Posts
    208

    Re: Convert Text to Time (not as simple as it sounds)

    Text to columns with the dash, put a space before am/pm with substitute() and add zero.
    That should do.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Convert Text to Time (not as simple as it sounds)

    Text to columns is fine if it's a on-off, but it's not really a long-term solution. It will depend on the OP's circumstances.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,714

    Re: Convert Text to Time (not as simple as it sounds)

    You've viewed our suggestions - some feedback would be appreciated.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,460

    Re: Convert Text to Time (not as simple as it sounds)

    In B16 and drag to the right:
    =TEXT((LEFT(B15,SEARCH("-",B15,1)-1)),"hh:mm:ss")

    In B17 and drag to the right:
    =TEXT((RIGHT(B15,SEARCH("-",B15,1)-1)),"hh:mm:ss")


    * Forgot to tell you I replaced with CNTRL+F all "pm" to " PM" (space+PM),
    and same for the "am" --> CTRL F--->Replace---> find what: "am"; replace with " AM" (with a space before AM)
    Attached Files Attached Files
    Last edited by belinda200; 06-20-2020 at 07:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cannot insert new worksheets.....sounds simple???
    By Victorthesecond in forum Excel General
    Replies: 4
    Last Post: 11-06-2018, 10:49 AM
  2. Formula that shows value between multiple number range.
    By HarrBarr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2015, 04:29 AM
  3. [SOLVED] How to find two values in one column (sounds so simple..)
    By FlorisL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2014, 04:04 PM
  4. Time stored as text. How can I convert to data and time?
    By matthewbutterworth in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2013, 10:19 AM
  5. Replies: 3
    Last Post: 03-02-2012, 08:18 PM
  6. [SOLVED] enter time in simple text and convert to hours/minutes
    By YanYan in forum Excel General
    Replies: 5
    Last Post: 06-22-2006, 10:10 PM
  7. convert time imported as text to time format for calculations
    By batfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1