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

1. 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.

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

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

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

Originally Posted by Pete_UK
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. 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. Re: Convert Text date with 3 character month name and 2 digit year to excel date

Does this work?

=--(1&A4)

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

Originally Posted by Bo_Ry
Does this work?

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

8. 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

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

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