Forum Discussion

Copper Contributor

Jun 09, 2023

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

excel

Formulas and Functions

OliverScheurich
Jun 09, 2023
Amitfre1
=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.

Amitfre1

Copper Contributor

Jun 09, 2023

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

OliverScheurich

Gold Contributor

Jun 09, 2023

Amitfre1

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

Amitfre1
Copper Contributor
Jun 10, 2023
OliverScheurich Hey, both work perfectly. Thank you so much!