+ Reply to Thread
Results 1 to 4 of 4

Recognize date using text formulas

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Recognize date using text formulas

    ;I have a large, imported worksheet which has dates in this format:

    Please Login or Register  to view this content.
    This is all in one cell.

    I need help converting this to an excel-recognized date and time (i.e. 4/01/09 4:00 PM)

    I've attached a sample spreadsheet. I've tried using LEN but my brain can't do the logical work required!

    Thanks!
    Attached Files Attached Files
    Last edited by jrtaylor; 12-19-2011 at 07:55 PM. Reason: Solved

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: need help getting excel to recognize date using text formulas

    This:
    =TEXT((LEFT(B6,FIND(" ",B6,FIND(" ",B6)+1)-1)&", "&MID(B6,LEN(B6)-7,4))+MID(B6,LEN(B6)-16,8),"m/dd/yy hh:mm AM/PM")

    Or just (this will require you format the cell to m/dd/yy hh:mm AM/PM):
    =(LEFT(B6,FIND(" ",B6,FIND(" ",B6)+1)-1)&", "&MID(B6,LEN(B6)-7,4))+MID(B6,LEN(B6)-16,8)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: Recognize date using text formulas

    Hey thanks so much! Perfect!

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Recognize date using text formulas

    Try also,

    =SUBSTITUTE(SUBSTITUTE(B6," ","," &LEFT(RIGHT(B6,9),6),2),RIGHT(B6,8),"")+0

    format as desired.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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