+ Reply to Thread
Results 1 to 11 of 11

Convert To Date and Time - exported CSV File Column not recognized as Date & Time

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    47

    Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Hi there,

    Simple problem here. A dataset exports from a system in .csv format. See attached column A where problem exists.
    I am not able to change this into a date. Even using the convert text to columns trick - with that Excel has a hard time with the dates where the day or month value is a single digit.
    is there a way I can easily change this date to just a date or a date and time separated?

    Having a bit of a hard time here.

    Thanks so much!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,008

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Two alternatives that worked with me, transforming it using Power Query (simple task) and using FILTERXML(), but I guess that the main problem here might be the american dating system.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-02-2017
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    47

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Quote Originally Posted by DJunqueira View Post
    Two alternatives that worked with me, transforming it using Power Query (simple task) and using FILTERXML(), but I guess that the main problem here might be the american dating system.
    I am not familiar with those - I have never used Power Query and I am on Excel 2016.
    Is there some other way to get around this? Perhaps a Macro?

    My country date format is 2021/11/25

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    All your examples show a time of 12:00:00 AM - is this always the case?

    Pete

  5. #5
    Registered User
    Join Date
    10-02-2017
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    47

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Quote Originally Posted by Pete_UK View Post
    All your examples show a time of 12:00:00 AM - is this always the case?

    Pete
    I am afraid not necessarily but the time is not important in the aspect of the report I need to generate, just the date so I can delete the time if needs be.

  6. #6
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,008

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Quote Originally Posted by Carlx12 View Post
    I am not familiar with those - I have never used Power Query and I am on Excel 2016.
    Is there some other way to get around this? Perhaps a Macro?
    My country date format is 2021/11/25
    Power Query can be installed into Excel 2013 and FILTERXML() is available since Excel 2013.

    https://support.microsoft.com/pt-br/...5-c1374812f5b7

    See the video about installing Power Query, it is a very good tool for Excel and is the same tool used in Power BI.
    https://www.youtube.com/watch?v=gwW2CDdvUUs
    Last edited by DJunqueira; 11-25-2021 at 08:52 AM.

  7. #7
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-02-2017
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    47

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    I am on Excel 2016.
    I presume Power Query is included in that and that if there is no other way I need to learn how to use it.
    I work for a very large global company whom does not allow any software or software addons installed that have not been fully vetted by our global IT team. Presently only Excel 2016 is allowed to be used.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    You can use this formula in B2:

    =DATE(MID(A2,FIND("/",A2,4)+1,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(MID(A2,FIND("/",A2),3),"/",""))

    Format as a date in the style you prefer, then copy down as required.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    10-02-2017
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    47

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Quote Originally Posted by Pete_UK View Post
    You can use this formula in B2:

    =DATE(MID(A2,FIND("/",A2,4)+1,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(MID(A2,FIND("/",A2),3),"/",""))

    Format as a date in the style you prefer, then copy down as required.

    Hope this helps.

    Pete
    Ah yes, thanks so much. That will do it for now. I am quite interested in Power Query and will spend some time learning how that works but the formula is a quick and easy enough solution to get me stuck into this report and get it done for my management team.
    Problem with working for such a large company is dealing with systems based in other countries which then uses their date formatting.
    Damn USA and their weird dating system!

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Convert To Date and Time - exported CSV File Column not recognized as Date & Time

    Glad to help, and thanks for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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. Replies: 2
    Last Post: 02-06-2020, 10:04 AM
  2. [SOLVED] How to convert Date and time format to only date in Column
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-29-2018, 08:28 AM
  3. Replies: 7
    Last Post: 06-19-2017, 06:10 PM
  4. Replies: 2
    Last Post: 11-09-2015, 01:54 PM
  5. [SOLVED] Convert Unix Date-time stamp to excel date and/or time columns
    By judikz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2015, 05:46 PM
  6. Convert Julian date/ time to regular date/ time (MM/DD/YYYY HH:MM:SS)
    By dataguy30 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2013, 11:33 AM
  7. [SOLVED] How do I convert imported date/time data to date/time format?
    By andykent99 in forum Excel General
    Replies: 4
    Last Post: 01-03-2013, 07:47 AM

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