Results 1 to 6 of 6

Thread: help with converting time from a text value

  1. #1
    Neophyte MonteJ's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    365

    help with converting time from a text value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to convert the time in each column from text. Any help would be appreciated. The timevalue formula hasn't worked for me.Chats.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    Time only? or date & time?

    In attached cell F2 has formula for time only from your text:
    Code:
    =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))+1,99),"am", " am"),"pm"," pm"))
    Cell K2 has the date only from your text:
    Code:
    =DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND(" ",TRIM(B2))+1,FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))-FIND(" ",TRIM(B2))-1),"st",""),"nd",""),"rd",""),"th",""))
    Cell P2 is just the last two added together.
    All values are now proper Excel times and dates (numbers).
    Attached Files Attached Files
    Last edited by p45cal; 2021-04-16 at 07:15 PM.

  3. #3
    Neophyte MonteJ's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Time only? or date & time?

    Time. Thank you for responding.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    Too late, you got both, but cell F2 gives just the time - see attachment in msg#2

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    Would a Power Query solution be of any use?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Would a Power Query solution be of any use?
    Obviously not.

Posting Permissions

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