Convert raw data to a date value

[Mia Story]

Hi,
Please advise how to convert a cell that includes the data along with other miscellaneous data to show just the date. I've tried a variety of formulas but those formulas just work with date characters, none include how to ignore miscellaneous characters, thought this formula would work 20190803 in AZ cell to =dateleft(AZ,4,mid(AZ,5,2),right(AZ,2)

The intention is to convert column B(date along with extra information) to Column D( just the date), see attached.

 

[Toadstool]

Hi Mia Story,

The third row of data looks like it has the wrong day but this formula should work:

MiaStory.xlsx
	B	C	D
1	Batchdate		convert to recognizable date
2	2019-03-20T12:44:56Z		3/20/2019
3	2020-03-05T12:44:56Z		3/5/2020
4	2020-03-01T12:44:56Z		3/1/2020
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

 

[Rick Rothstein]

To get just the date without any time portion, this short formula will work...

=0+LEFT(B2,10)

Note, though, that you will have to format the cell to display the date format of your choice.

 

[Mia Story]

Hi Mia Story,

The third row of data looks like it has the wrong day but this formula should work:

MiaStory.xlsx
	B	C	D
1	Batchdate		convert to recognizable date
2	2019-03-20T12:44:56Z		3/20/2019
3	2020-03-05T12:44:56Z		3/5/2020
4	2020-03-01T12:44:56Z		3/1/2020
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

 

[Mia Story]

Hi Mia Story,

The third row of data looks like it has the wrong day but this formula should work:

MiaStory.xlsx
	B	C	D
1	Batchdate		convert to recognizable date
2	2019-03-20T12:44:56Z		3/20/2019
3	2020-03-05T12:44:56Z		3/5/2020
4	2020-03-01T12:44:56Z		3/1/2020
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

Hi Fluff, This is great!! I appreciate it so much!! Exactly what I was looking for.

 

[Rick Rothstein]

Why are you thanking Fluff... he is not involved in this thread. You should be thanking Toadstool as he is the one who posted the formula you said you are using. As an aside, I am just wondering if you tried the more compact formula that I posted in Message #3 yet?

 

[Toadstool]

Why are you thanking Fluff... he is not involved in this thread. You should be thanking Toadstool as he is the one who posted the formula you said you are using. As an aside, I am just wondering if you tried the more compact formula that I posted in Message #3 yet?

It's OK Rick. Fluff always gets the praise.