Forum Discussion
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 |
=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.
This is 365 only. I might also have missed some irregularities in the 'text' data you wish to process.
= DATEVALUE(TEXTAFTER(text, "-"))=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")Does this return the intended result?
=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")
Does it work with YY instead of JJ?
