+ Reply to Thread
Results 1 to 4 of 4

Chaning date and time (2016-02-25T08:16:22Z) to a more readable format

  1. #1
    Registered User
    Join Date
    03-22-2016
    Location
    Hampshire, England
    MS-Off Ver
    Office 365
    Posts
    1

    Chaning date and time (2016-02-25T08:16:22Z) to a more readable format

    I'm using an XML data feed to bring in some RSS feed data into excel.

    The date and time format are text and displayed like this:

    2016-02-25T08:16:22Z

    What I'm really looking for is a way to best display in the format:

    dd-mm-yyyy HH:MM

    Which would look like this:

    25-02-2016 08:16

    I've had a quick play with the custom number setting but haven't been successful. What's the best way to do this, keeping in mind that the XML data will refresh so it's probably not so practical to add in another column with formulas.

    Thanks!!!

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

    Re: Chaning date and time (2016-02-25T08:16:22Z) to a more readable format

    Quote Originally Posted by Mark_T View Post
    2016-02-25T08:16:22Z
    Excel sees that as a TEXT string.

    Trying to change formats will not work.

    You need either a formula or a macro.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Chaning date and time (2016-02-25T08:16:22Z) to a more readable format

    =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2);MID(A1,18,2))

    Try this formula
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,908

    Re: Chaning date and time (2016-02-25T08:16:22Z) to a more readable format

    Or try this...
    =TEXT(LEFT(A1,10)+MID(A1,12,8),"dd-mm-yyyy HH:MM")

+ 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: 5
    Last Post: 10-16-2015, 09:18 AM
  2. Unable to do data parsing into readable and useful format in Office 2010
    By theo28 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2014, 04:29 AM
  3. Help in putting data in readable format
    By theo28 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2014, 09:36 AM
  4. Help Turning Mass of Data into Readable Format
    By dvent in forum Excel General
    Replies: 1
    Last Post: 03-22-2010, 06:33 AM
  5. [SOLVED] how do I convert utc to a readable time format?
    By Jon in forum Excel General
    Replies: 2
    Last Post: 06-08-2006, 09:45 AM
  6. [SOLVED] Convert Unix timestamp to Readable Date/time
    By Emily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 08:00 PM
  7. [SOLVED] converting PDF file to readable excel format
    By TNMAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2005, 09:06 PM

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