Forum Discussion
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.
=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")
Does it work with YY instead of JJ?
OliverScheurich It does, thank you so much! However, I cannot change the date format at all. is there a way to overcome this? Thanks
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.
OliverScheurich Thank you!
2 problems that I still have:
- The formula does not apply for years 2000's and 90's and so on.. for example-
12026-09-10 866-03-08 - I also have these types of strings which should return the year only-
5904-06 58224-07 2209-04 3529-08
- The formula does not apply for years 2000's and 90's and so on.. for example-