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

chindog

New Member
Joined
Jan 26, 2010
Messages
2
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?
 

Some videos you may like

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
Joined
May 26, 2018
Messages
196
My spreadsheet layout is given below.

1612838020702.png


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
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jan 26, 2010
Messages
2
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
Joined
Jun 29, 2014
Messages
5,847
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
Just curious, which version of the formulas worked for you?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top