+ Reply to Thread
Results 1 to 8 of 8

Convert Text date with 3 character month name and 2 digit year to excel date

  1. #1
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Convert Text date with 3 character month name and 2 digit year to excel date

    The issue I'm having is converting imported text in the format "Jul-21" to a date format excel recognizes as the 1st date of the month from the given text. For example, "Jul-21" should be "July 1, 2021".

    When I try using the methods I've read online I get "July 21, 2019" or I can get it to text, but can't figure out how to convert it to a date.

    I'm sure there is a simple method, but I can't figure it out. A worksheet with my attempt using "texttocolumns" and "datevalue" is attached.
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    Assuming text in A1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    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 Text date with 3 character month name and 2 digit year to excel date

    You can use this formula (say in B4):

    =IF(A4="","",DATE(IF(--RIGHT(A4,2)>30,"19","20")&RIGHT(A4,2),MATCH(LEFT(A4,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),1))

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

    Note that this treats years up to 30 as being in this century, and years 31 to 99 as belonging to the 1900's.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    Quote Originally Posted by Pete_UK View Post
    You can use this formula (say in B4):

    =IF(A4="","",DATE(IF(--RIGHT(A4,2)>30,"19","20")&RIGHT(A4,2),MATCH(LEFT(A4,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),1))

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

    Note that this treats years up to 30 as being in this century, and years 31 to 99 as belonging to the 1900's.

    Hope this helps.

    Pete
    Yes, that does it! Looks like what you did was look at the right 2 digits and either make them 19 or 20 to start, then add the 2 digits already there (ex if it has 21 now you put 2021. Then you looked at the left 3 values and used them to find a value from an array with the 12 months and put 1 for the day of the month. I had no idea this would be so complicated, but I'm glad it works. Makes things much easier than retyping all those values!

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

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    Another way:

    =IF(A4="","",--REPLACE(A4,4,1,"1, 20"))

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    Does this work?

    =--(1&A4)

  7. #7
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    Quote Originally Posted by Bo_Ry View Post
    Does this work?

    =--(1&A4)
    Yes! Can you explain the "--" in front of these formulas? I've never seen that before

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Convert Text date with 3 character month name and 2 digit year to excel date

    -- is close to value() or datevalue() change text to value, but a bit more that can change =--True to 1 and =--False to 0

    =value(1&A4) or =datevalue(1&A4) should work the same
    Last edited by Bo_Ry; 07-11-2019 at 11:31 AM.

+ 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. [SOLVED] Convert Text Year and Month to Date Format
    By Barieq in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2017, 02:07 AM
  2. [SOLVED] how to convert text number to year month and date
    By leakhna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2015, 05:19 AM
  3. Need to convert Month/Date/Year to Year/Month/Date so excel will recognize
    By juliettelam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2014, 04:17 PM
  4. [SOLVED] Extract Text from String then Convert to a Date (Month/Year)
    By sgrey24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2013, 06:11 PM
  5. [SOLVED] Use TEXT function as Criteria on Sumifs to convert month/year to date format
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2012, 07:49 PM
  6. Convert date to month/year text
    By FindAnswers in forum Excel General
    Replies: 2
    Last Post: 11-07-2012, 03:42 PM
  7. Result of date as Month-2 digit Year
    By Linda in forum Excel General
    Replies: 3
    Last Post: 06-04-2005, 12:05 PM

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