+ Reply to Thread
Results 1 to 11 of 11

Convert Text Dates to Date Values

  1. 01-16-2020, 08:27 PM #1
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Thumbs up Convert Text Dates to Date Values

    Hi

    Have the following dates as text dates! How to convert date value?

    Thought this work but missing something:

    =DATEVALUE(A1)

    Column A

    January 1 2020
    January 20 2020
    January 24 2020
    February 2 2020
    February 12 2020
    February 14 2020
    February 17 2020
    February 25 2020
    March 8 2020


    Thanks
    Last edited by mycon73; 01-16-2020 at 08:53 PM.
    MyCon
    -- Using Latest Version of Excel
    Reply With QuoteRegister To Reply
  2. 01-16-2020, 08:45 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,720

    Re: Convert Text Dates to Date Values

    Try this in B1:

    =DATE(RIGHT(A1,4),MID(A1,FIND(" ",A1)+1,2),(FIND(LEFT(A1,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1)

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

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 01-16-2020, 08:51 PM #3
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Convert Text Dates to Date Values

    Hi Pet UK

    Wow!! Quite the funky function but it works!!

    Thanks for the assistance
    Reply With QuoteRegister To Reply
  4. 01-16-2020, 08:59 PM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,720

    Re: Convert Text Dates to Date Values

    Glad it worked for you, and thanks for the rep. The DATE function is more reliable across continents, but there are probably other ways it could be done.

    Pete
    Reply With QuoteRegister To Reply
  5. 02-15-2020, 01:31 PM #5
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Convert Text Dates to Date Values

    Hi Pete_UK & Others

    Just getting back to this and thought this was resolved...

    After further review, noticed some of the converted text dates to date values are radically wrong!

    -- See attached example

    How to fix this?

    Thanks
    Attached Files Attached Files
    Last edited by mycon73; 02-15-2020 at 01:39 PM.
    Reply With QuoteRegister To Reply
  6. 02-15-2020, 01:43 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Convert Text Dates to Date Values

    Swap round the day identifier and the month identifier:

    =IF(B10="","",DATE(RIGHT(B10,4),(FIND(LEFT(B10,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1,MID(B10,FIND(" ",B10)+1,2)))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 02-15-2020, 01:46 PM #7
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Convert Text Dates to Date Values

    Aligw

    That looks a lot better!!!

    Thanks for the assistance
    Reply With QuoteRegister To Reply
  8. 02-15-2020, 01:48 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Convert Text Dates to Date Values

    No problem.

    The DATE function requires year, month and date in that order.
    Last edited by AliGW; 02-15-2020 at 01:52 PM.
    Reply With QuoteRegister To Reply
  9. 02-15-2020, 01:53 PM #9
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Convert Text Dates to Date Values

    .another variation

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

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  10. 02-15-2020, 02:34 PM #10
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,720

    Re: Convert Text Dates to Date Values

    The formula has the days and months in the wrong place (sorry about that) - put this in C10:

    =IF(B10="","",DATE(RIGHT(B10,4),(FIND(LEFT(B10,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1,MID(B10,FIND(" ",B10)+1,2)))

    then copy it down.

    Hope this helps.

    Pete

    EDIT: I hadn't refreshed the screen from before dinner - I see you have had other replies while I ate.

    Pete
    Last edited by Pete_UK; 02-15-2020 at 02:36 PM.
    Reply With QuoteRegister To Reply
  11. 02-15-2020, 03:00 PM #11
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Convert Text Dates to Date Values

    Aligw, Richard Buttrey and Pete_UK

    All work well - Thanks again for the assistance
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dates stored as Text and Values - convert entire column to date?
    By happydays886 in forum Excel General
    Replies: 12
    Last Post: 01-05-2018, 12:13 PM
  2. [SOLVED] convert text dates to values
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-20-2017, 12:52 PM
  3. [SOLVED] How do I convert column of dates into column of date values?
    By jfm854 in forum Excel General
    Replies: 7
    Last Post: 12-09-2013, 04:06 PM
  4. Convert Text Dates & Time to Values?
    By mycon73 in forum Excel General
    Replies: 9
    Last Post: 01-09-2013, 11:39 PM
  5. Convert Text Dates, Perform Calculation, Convert back to Text
    By Orada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2012, 05:25 PM
  6. Convert Text Values To Date and Time
    By aftabn10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2012, 09:50 AM
  7. [SOLVED] Request a Macro to Convert data into Dates (Multiple Dates Values separated by Line)
    By seenai in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2012, 02:28 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1