How do I change a date stored as text to an acutal date format?

[Ruthie]

Happy Friday everyone!

I need to compare a date in date format to a date listed as text format, in order to compare these 2 sets of data and write an if statement. How do I convert a date stored as text to short date?

WRITTEN_DT
03132014
(General Format - number stored as text)​

START DATE
1/1/2014
(Date Format - number stored as short date)​

Date formatting will be the death of me. But I secretly love that I have another problem to bring to Chandoozers who are some of the best people out there!

 

[SirJB7]

Hi, Ruthie!

Let us go by parts, as Jack used to say.

If a cell formatted as general contains 03132014 it'll be a numeric value and not text, unless the format was applied after getting the value in the cell or if you preceded it with an apostrophe.

Said so, and assuming that it actually holds text value (which you can check with an =ISNUMBER(A1) at any empty cell and if A1 is the related cell), then you can try this:
=FECHA(DERECHA(A1;4);IZQUIERDA(A1;2);EXTRAE(A1;3;2)) -----> in english: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))

Regards!

 

[bobhc]

Good day Ruthie

Go to the ribbon and format as short date the cell that is text, if you are sure it is text!

 

[Luke M]

One trick that sometimes forgotten about is to use the Text-to-Columns. TtC can automatically convert your dates for you, even if the column contains numbers and text (but should be all dates). Select your column, go to Data - Text to Columns. In the Wizard that pops up, select Delimited on First Screen, and just click Next on the 2nd screen. On the 3rd screen, Under "Column Data Format", select Date and choose "MDY" from dropdown. Hit Finish, and you're done!

 

[Ruthie]

SirJb7- I was incorrect; the data was in general format. With that new assumption, what would be the formula for doing that? I want to try to learn the longhand, though Luke M's option worked wonderfully.

You guys are the best!

 

[SirJB7]

Hi, Ruthie!

If it was in general format we have 2 possibilities:
a) numeric content: it should have displayed 3132014 without the leading zero
b) text content: then my posted formula does the job, as i works on a string to build a valid date, that should be displayed with your default system date format

If none of these happen to be true, consider uploading (a) sample file(s) (including manual examples of desired output if applicable) to check it properly.
Regards!