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

**Darth269** · 06-20-2020, 04:26 AM

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.

**AliGW** · 06-20-2020, 04:55 AM

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

**Darth269** · 06-20-2020, 05:07 AM

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

**AliGW** · 06-20-2020, 05:19 AM

In the meantime, a PowerQuery solution.

M Code:

Please Login or Register  to view this content.

**alansidman** · 06-20-2020, 06:02 AM

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

**RaulSerg** · 06-20-2020, 06:21 AM

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

**AliGW** · 06-20-2020, 06:29 AM

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.

**AliGW** · 06-20-2020, 07:21 AM

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.

**Limor_OP** · 06-20-2020, 07:44 AM

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)

**Darth269** · 06-17-2021, 07:07 AM

thank you all for your help and suggestions with this, I'm afraid the project evolved and I never got a chance to test/implement any of these, but I'm so very grateful for the time taken to provide assistance.

**AliGW** · 06-17-2021, 07:21 AM

You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Cannot insert new worksheets.....sounds simple???

Formula that shows value between multiple number range.

[SOLVED] How to find two values in one column (sounds so simple..)

Time stored as text. How can I convert to data and time?

how to calculate difference of time from simple text, and add/multiply time.

[SOLVED] enter time in simple text and convert to hours/minutes

[SOLVED] convert time imported as text to time format for calculations

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions