Convert raw data to a date value

Mia Story

New Member
Joined
May 8, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
    RawData to Date.PNG
    12 KB · Views: 4

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
993
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 8, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
993
Office Version
  1. 2016
Platform
  1. Windows
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.
;)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,600
Members
410,625
Latest member
Hemantjain86
Top