+ Reply to Thread
Results 1 to 9 of 9

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

  1. 06-20-2020, 04:26 AM #1
    Darth269
    Darth269 is offline
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    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.
    Reply With QuoteRegister To Reply
  2. 06-20-2020, 04:55 AM #2
    AliGW
    AliGW is online now
    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
    46,873

    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.
    Reply With QuoteRegister To Reply
  3. 06-20-2020, 05:07 AM #3
    Darth269
    Darth269 is offline
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    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
    Reply With QuoteRegister To Reply
  4. 06-20-2020, 05:19 AM #4
    AliGW
    AliGW is online now
    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
    46,873

    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
    Reply With QuoteRegister To Reply
  5. 06-20-2020, 06:02 AM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,396

    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
    Let me know if that works for you

    Alan


    Have you debugged your code?
    Database Normalization
    Database Principles
    Move Data from Excel to Access
    What is Power Query
    Reply With QuoteRegister To Reply
  6. 06-20-2020, 06:21 AM #6
    RaulSerg
    RaulSerg is offline
    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.
    Reply With QuoteRegister To Reply
  7. 06-20-2020, 06:29 AM #7
    AliGW
    AliGW is online now
    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
    46,873

    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.
    Reply With QuoteRegister To Reply
  8. 06-20-2020, 07:21 AM #8
    AliGW
    AliGW is online now
    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
    46,873

    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.
    Reply With QuoteRegister To Reply
  9. 06-20-2020, 07:44 AM #9
    belinda200
    belinda200 is offline
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,965

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. how to calculate difference of time from simple text, and add/multiply time.
    By MastahFez in forum Excel General
    Replies: 3
    Last Post: 03-02-2012, 08:18 PM
  6. 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. [SOLVED] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1