Results 1 to 2 of 2

Thread: How do i extract date info from varying length text string?

  1. #1

    Lightbulb How do i extract date info from varying length text string?



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

    I have imported data in an XML file into EXCEL that doesn't seem to be formatting correctly as a Date and appears to be remaining as 'General' formatting. The date and time strings are shown in US format (m/d/yy) by default and are either "7/3/17 3:40 AM" or "7/13/17 10:13 AM". When I use the DATEVALUE formula on the shorter dates like 7/3/17 it pulls the correct European date of 3rd July 2017 out as 03/07/2017 but the DATEVALUE function doesn't recognise the 7/13/17 dates which have one extra digit on the middle day numbers.

    I have tried to use the MID and LEFT functions to pull out the date numbers but the formula I have been using doesn't cope when the extra digit is added in for say 13th of the month vs 3rd of the month (or similarly the 6th month of the year vs the 11th month of the year).

    The formula I have been using is :-

    =DATE(MID('XML Combined Account Statement'!D17,5,2)+2000,LEFT('XML Combined Account Statement'!D17,1),MID('XML Combined Account Statement'!D17,3,1))

    The format of the data is shown below so I can't use the RIGHT command either as the number of digits in the TIME data also changes. Wondering if anyone has any ideas on where I am going wrong with this?

    10/6/17 6:01 AM
    10/5/17 1:48 PM
    10/10/17 2:39 AM
    10/5/17 2:48 PM
    10/11/17 10:14 AM
    10/5/17 10:47 PM
    10/6/17 2:44 PM
    7/5/17 3:32 PM
    7/3/17 1:47 AM
    7/7/17 6:31 AM
    7/3/17 3:40 AM
    7/6/17 11:07 AM
    7/3/17 4:57 AM
    7/13/17 10:13 AM
    7/5/17 1:51 PM
    7/12/17 11:00 PM
    7/5/17 2:00 PM
    7/7/17 5:47 AM
    7/5/17 2:00 PM

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Try Data - Text to columns - Use the space as delimiter - In the last window of the wizard select the date format as required (DMY or MDY) for the first column and " do not import" for other columns - Finish
    Or try = DATEVALUE(left(a1,len(a1)-find(" ",A1)))
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

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