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

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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
996
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,420
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,420
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
996
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,114,111
Messages
5,546,000
Members
410,720
Latest member
SSL
Top