Converting a text cell that contains a text date to an actual excel date

[chindog]

I searched for a solution, but didn't find one for this issue.

I have a text cell that contains "09/28/21 - (Tuesday, after rally ends)". This is in a multiple choice form, so other cells will have similar text data, but different dates. I need to convert these text cells to dates. I thought it would be easy since the first 8 characters of the cell contain the dates. But nothing is working, and my results cell just returns #VALUE. I have tried the Trunc function, and datevalue function, and combinations of them. Any ideas?

 

[Saba Sabaratnam]

My spreadsheet layout is given below.

Enter the following formula in B1

=DATE("20"&MID(A1,8,2),MID(A1,2,2),MID(A1,5,2))

Please note that the date is presented in Aus date format.

Kind regards

Saba

 

[jtakw]

Hi,

DATEVALUE should work, maybe you have rogue spaces and/or hidden characters in the cell, try these:

Book3.xlsx
	A	B	C	D	E	F
1	09/28/21 - (Tuesday, after rally ends)	9/28/2021	9/28/2021	9/28/2021	9/28/2021	9/28/2021
Sheet747

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(LEFT(A1,8))
C1	C1	=DATEVALUE(LEFT(TRIM(A1),8))
D1	D1	=DATEVALUE(LEFT(CLEAN(A1),8))
E1	E1	=DATEVALUE(LEFT(CLEAN(TRIM(A1)),8))
F1	F1	=LEFT(A1,8)+0

 

[chindog]

Hi,

DATEVALUE should work, maybe you have rogue spaces and/or hidden characters in the cell, try these:

Book3.xlsx
	A	B	C	D	E	F
1	09/28/21 - (Tuesday, after rally ends)	9/28/2021	9/28/2021	9/28/2021	9/28/2021	9/28/2021
Sheet747

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(LEFT(A1,8))
C1	C1	=DATEVALUE(LEFT(TRIM(A1),8))
D1	D1	=DATEVALUE(LEFT(CLEAN(A1),8))
E1	E1	=DATEVALUE(LEFT(CLEAN(TRIM(A1)),8))
F1	F1	=LEFT(A1,8)+0

Thanks! This worked!

 

[jtakw]

You're welcome, thanks for the feedback.
Just curious, which version of the formulas worked for you?