+ Reply to Thread
Results 1 to 6 of 6

Convert Time in Text format to time

  1. #1
    Registered User
    Join Date
    04-27-2020
    Location
    NYC
    MS-Off Ver
    10
    Posts
    2

    Question Convert Time in Text format to time

    Hello there,

    Thank you for getting to my question.

    I have time in the following text format and want to convert to time and derive the AM PM based on the last alphabet.

    For example:

    0819A - 08:19 AM
    1258P - 12:58 PM
    0301P - 03:01 PM
    1006A - 10:06 AM
    0502P - 05:02 PM

    Can someone please help?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Convert Time in Text format to time

    With time in A1

    =RIGHT(A1,8)+0

    Format as "hh:mm am/pm"
    Attached Images Attached Images
    Last edited by bebo021999; 10-28-2020 at 09:26 PM. Reason: image added

  3. #3
    Registered User
    Join Date
    04-27-2020
    Location
    NYC
    MS-Off Ver
    10
    Posts
    2

    Re: Convert Time in Text format to time

    Oh I am sorry, I dint explain properly. I meant my column has values as below(Column A) and I want the value under the result column.

    Column A RESULT

    0819A 08:19 AM
    1258P 12:58 PM
    0301P 03:01 PM
    1006A 10:06 AM
    0502P 05:02 PM

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Convert Time in Text format to time

    It's still no clearer, but I think you have 0819A in A1, so try this in B1:

    =LEFT(A1,2)/24 + MID(A1,3,2)/24/60 + (RIGHT(A1)="P")*0.5

    Format the cell as "hh:mm am/pm"

    Hope this helps.

    Pete

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: Convert Time in Text format to time

    Please try

    =--TEXT(LEFT(A2,4),"0\:00 "&RIGHT(A2))

    and format cell to hh:mm am/pm

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Convert Time in Text format to time

    Try again:

    Please Login or Register  to view this content.
    Apply for both cases: 0301P or 301P (hmmP)

+ 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. Replies: 2
    Last Post: 02-06-2020, 10:04 AM
  2. text convert to time format
    By makinmomb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-10-2014, 02:18 PM
  3. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  4. [SOLVED] How to convert text into time format?
    By adelkam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2012, 09:39 PM
  5. Convert text to time format
    By maverick_abhi in forum Excel General
    Replies: 2
    Last Post: 07-27-2006, 07:20 AM
  6. [SOLVED] How do I convert a time to text format.
    By Rick in forum Excel General
    Replies: 4
    Last Post: 02-20-2006, 07: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1