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

#### chindog

##### New Member
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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Saba Sabaratnam

##### Board Regular
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

##### Well-known Member
Hi,

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

Book3.xlsx
ABCDEF
109/28/21 - (Tuesday, after rally ends)9/28/20219/28/20219/28/20219/28/20219/28/2021
Sheet747
Cell Formulas
RangeFormula
B1B1=DATEVALUE(LEFT(A1,8))
C1C1=DATEVALUE(LEFT(TRIM(A1),8))
D1D1=DATEVALUE(LEFT(CLEAN(A1),8))
E1E1=DATEVALUE(LEFT(CLEAN(TRIM(A1)),8))
F1F1=LEFT(A1,8)+0

#### chindog

##### New Member
Hi,

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

Book3.xlsx
ABCDEF
109/28/21 - (Tuesday, after rally ends)9/28/20219/28/20219/28/20219/28/20219/28/2021
Sheet747
Cell Formulas
RangeFormula
B1B1=DATEVALUE(LEFT(A1,8))
C1C1=DATEVALUE(LEFT(TRIM(A1),8))
D1D1=DATEVALUE(LEFT(CLEAN(A1),8))
E1E1=DATEVALUE(LEFT(CLEAN(TRIM(A1)),8))
F1F1=LEFT(A1,8)+0
Thanks! This worked!

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.
Just curious, which version of the formulas worked for you?

Replies
5
Views
147
Replies
6
Views
74
Replies
17
Views
164
Replies
4
Views
144
Replies
16
Views
136

1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back