# Convert raw data to a date value

#### Mia Story

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

#### Attachments

• RawData to Date.PNG
12 KB · Views: 4

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

##### Well-known Member
Hi Mia Story,

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

MiaStory.xlsx
BCD
1Batchdateconvert to recognizable date
22019-03-20T12:44:56Z3/20/2019
32020-03-05T12:44:56Z3/5/2020
42020-03-01T12:44:56Z3/1/2020
Sheet1
Cell Formulas
RangeFormula
D2:D4D2=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

#### Mia Story

##### New Member
Hi Mia Story,

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

MiaStory.xlsx
BCD
1Batchdateconvert to recognizable date
22019-03-20T12:44:56Z3/20/2019
32020-03-05T12:44:56Z3/5/2020
42020-03-01T12:44:56Z3/1/2020
Sheet1
Cell Formulas
RangeFormula
D2:D4D2=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

#### Mia Story

##### New Member

Hi Mia Story,

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

MiaStory.xlsx
BCD
1Batchdateconvert to recognizable date
22019-03-20T12:44:56Z3/20/2019
32020-03-05T12:44:56Z3/5/2020
42020-03-01T12:44:56Z3/1/2020
Sheet1
Cell Formulas
RangeFormula
D2:D4D2=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

##### MrExcel MVP
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?

##### Well-known Member
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.

Replies
1
Views
44
Replies
7
Views
94
Replies
4
Views
125
Replies
9
Views
290
Replies
4
Views
63