+ Reply to Thread
Results 1 to 5 of 5

Need help converting date and time from odd text to standard format

  1. #1
    Registered User
    Join Date
    05-10-2014
    MS-Off Ver
    Excel 2003
    Posts
    1

    Need help converting date and time from odd text to standard format

    Hi all and thanks in advance for you help. Have searched here and elsewhere for a solution, but had no luck.

    I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.

    Thanks!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Need help converting date and time from odd text to standard format

    Hi,

    What do you mean by "entries like this"? Are they always of that precise format, even including the "On" at the start?

    If not, can you give enough examples so as to cover all variations in the strings we're dealing with?

    Regards
    Last edited by XOR LX; 05-10-2014 at 05:58 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Need help converting date and time from odd text to standard format

    Ugh, this is so horrible but it works based on your example.
    It detects spaces.

    =DATEVALUE(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),1))+1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))+1-(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),1))+1)-1)&" "&MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3))+1-(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))+1)-1))&" "&RIGHT(A1,LEN(A1)-(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1)+1))

    As Im typing this I'm now thinking if the format is exactly the same (as is being queried above) just use Text to columns and use spaces as delimiters then DATEVALUE()
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,890

    Re: Need help converting date and time from odd text to standard format

    This one is horrible, too...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help converting date and time from odd text to standard format

    This will extract the date as long as the date is always on the left end of the string before the time.

    If no year number is present then the formula will use the current year.

    =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

    Format as Date

    It will work on a variety of date formats EXCEPT when the month name comes first:

    On May 14 at 8:00

    Data Range
    A
    B
    1
    On 14 May at 8:00
    5/14/2014
    2
    On 31 Dec 2010 at 12 noon
    12/31/2010
    3
    10/22 is a good date
    10/22/2014
    4
    10-22 is a good date
    10/22/2014
    5
    On May 14 at 8:00
    1/14/1900
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. [SOLVED] Converting Text to Military Date Time Group Format
    By ET1CARNES in forum Excel General
    Replies: 9
    Last Post: 04-12-2014, 08:51 AM
  2. [SOLVED] Converting Standard Time format (hh:mm:ss) to number of minutes
    By xygrax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-27-2014, 10:54 AM
  3. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  4. Converting all date formats to one single standard format.
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2010, 07:14 PM
  5. converting "strange" date format to standard date format
    By perinouk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2007, 02:09 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