+ Reply to Thread
Results 1 to 8 of 8

Convert Text to Excel Date

  1. #1
    Registered User
    Join Date
    03-11-2017
    Location
    Cairo, Egypt
    MS-Off Ver
    2016
    Posts
    11

    Convert Text to Excel Date

    Hi There,

    I am extracting some dates from a system. However, Excel doesn't recognize it as a date field.
    Is there any solution please to this issue by using only formulas? (not through text to column and not through formatting cells)
    Sample sheet attached.

    dd1.png

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,416

    Re: Convert Text to Excel Date

    Select all the dates. Use Data | Text to Columns | Delimited, Next, Next, Date: DMY, Finish
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    03-11-2017
    Location
    Cairo, Egypt
    MS-Off Ver
    2016
    Posts
    11

    Re: Convert Text to Excel Date

    Quote Originally Posted by TMS View Post
    Select all the dates. Use Data | Text to Columns | Delimited, Next, Next, Date: DMY, Finish
    Hi TMS, Thanks. But as mentioned in my post, I am looking for a formula. (not text to column) as I am going to extract this data from a system many times every day to update a report.
    Text to column will not be practical in my case.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,416

    Re: Convert Text to Excel Date

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

  5. #5
    Registered User
    Join Date
    03-11-2017
    Location
    Cairo, Egypt
    MS-Off Ver
    2016
    Posts
    11

    Re: Convert Text to Excel Date

    It didnít work. I got a #VALUE! error

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

    Re: Convert Text to Excel Date

    Try this:

    =DATE(RIGHT([@Date],4),MID([@Date],4,2),LEFT([@Date],2))

  7. #7
    Registered User
    Join Date
    03-11-2017
    Location
    Cairo, Egypt
    MS-Off Ver
    2016
    Posts
    11

    Re: Convert Text to Excel Date

    Quote Originally Posted by Phuocam View Post
    Try this:

    =DATE(RIGHT([@Date],4),MID([@Date],4,2),LEFT([@Date],2))
    Thank you very much.. it worked!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,416

    Re: Convert Text to Excel Date

    Not sure why it didn't work for you. The sample you provided converted OK.
    Attached Files Attached Files

+ 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 text date to Excel format
    By MiteshChuahan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2020, 07:05 PM
  2. Replies: 7
    Last Post: 07-11-2019, 11:27 AM
  3. [SOLVED] Convert text date in to a date format excel can read
    By BC TIPPING in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-03-2017, 02:49 AM
  4. Replies: 6
    Last Post: 10-13-2017, 03:32 PM
  5. [SOLVED] How to convert exported text date to desired date format with excel vba.
    By sktneer in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-03-2013, 01:08 AM
  6. [SOLVED] Convert date text to Excel date
    By kborgers in forum Excel General
    Replies: 6
    Last Post: 06-19-2012, 01:35 PM
  7. [SOLVED] How do I convert a number formated as a date to text in Excel?
    By BrotherNov in forum Excel General
    Replies: 6
    Last Post: 03-02-2005, 12: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