SOLVED

# converting text to date format

I need to convert the following text format into a date format so that the 1st sequence of numbers will be deleted, the 2nd sequence will be treated as the month, and the 3rd will be the year.

See the example below. How should I use the text-to-columns function properly to reach that?

 30858-05-22 May-22 37020-11-22 November-22 14858-06-22 June-22 30333-01-22 January-22 65113-04-21 April-21 6165-07-16 July-16
9 Replies

# Re: converting text to date format

``=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")``

Does this return the intended result? # Re: converting text to date format

@OliverScheurich yes for the month, not for the year.. # Re: converting text to date format

=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")

Does it work with YY instead of JJ?

# Re: converting text to date format

@OliverScheurich It does, thank you so much! However, I cannot change the date format at all. is there a way to overcome this? Thanks

# Re: converting text to date format

You are welcome. In cell B1 you can apply this formula as well:

=TEXT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)),"MMMM-YY")

In cell C1 is this formula which returns the date:

=DATEVALUE(B1)

The number format of the cells in column C is date ("Datum"). Or you can do this in one step:

=DATEVALUE(TEXT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)),"MMMM-YY"))

The result is returned in column D.

# Re: converting text to date format

@OliverScheurich Thank you!

2 problems that I still have:

1. The formula does not apply for years 2000's and 90's and so on.. for example-
 12026-09-10 866-03-08
2. I also have these types of strings which should return the year only-
 5904-06 58224-07 2209-04 3529-08
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: converting text to date format

``=DATE(NUMBERVALUE(RIGHT(A1,2))+IF(NUMBERVALUE(RIGHT(A1,2))>25,1900,2000),NUMBERVALUE(MID(A1,7,2)),1)``

An alternative could be this formula. Since it's unknown if e.g. 45145-02-11 is february 2011 or february 1911 i've added an IF statement which returns the 90s or 80s.... if the last two digits are greater 25. The 2000's are returned if the last two digits are 25 or less. ``=DATE(NUMBERVALUE(RIGHT(A1,2))+IF(NUMBERVALUE(RIGHT(A1,2))>25,1900,2000),1,1)``

For the second question you can try this formula.

# Re: converting text to date format

This is 365 only.  I might also have missed some irregularities in the 'text' data you wish to process.

``= DATEVALUE(TEXTAFTER(text, "-"))`` # Re: converting text to date format

@OliverScheurich Hey, both work perfectly. Thank you so much!