+ Reply to Thread
Results 1 to 6 of 6

Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    2

    Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    I've been reading around and there seem to be a lot of formulas to fix parts of my date problem but not sure how to combine them all to get a date that excel recognizes. Would like to remove the time, including the AM or PM and then convert the Month (Jan) into the date (1). Once that is out of there DATEVALUE should do the trick. Any ideas?

    Thanks in advance for the help, this one has me stumped...

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    =--SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", ", ", 2) and format as you wish.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    Friend ,please find below formula.

    B5=your cell range of date (Jan/07/2013 11:55:43 AM )
    =SUBSTITUTE(MID(B5,1,FIND(" ",B5)-1),MID(MID(B5,1,FIND(" ",B5)-1),1,FIND("/",MID(B5,1,FIND(" ",B5)-1))-1),MONTH(1&MID(B5,1,FIND("/",B5)-1)))

    I hope your problem is resolved

  4. #4
    Registered User
    Join Date
    03-13-2014
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    Perfect, thank you my friend!

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    Hi dynastarsic,

    I'm sure SHG wont mind me making one change to his formula.

    =--INT(SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", ", ", 2))

    Adding the INT() function gets rid of the time at the end which you said you didn't need.

    Regards,

    David


    When you reply please make it clear WHO you are responding to by mentioning their name.

    If this has been of assistance, please advise. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

    Thank you, David. You can remove the --.

+ 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. Convert Date (i.e. 8/1/2013) to Text Month (i.e. August)
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2013, 06:42 AM
  2. [SOLVED] Month format issue - convert 9/1/2013 into Sep
    By Sam Capricci in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2013, 09:43 AM
  3. Simple formula to display a date (10/19/2013) as text (October 2013)
    By benyben123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2013, 03:47 AM
  4. Replies: 10
    Last Post: 08-01-2013, 11:40 AM
  5. Replies: 3
    Last Post: 01-03-2013, 04:58 PM

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