Convert Text String to Date

dls0406

New Member
Joined
Oct 7, 2019
Messages
15
I have a text string containing either 7 or 8 (MDDYYYY or MMDDYYYY) digits which I want to convert to a date value. I can use the Text.PadStart to convert all of the text string to MMDDYYYY, but am still having a lot of trouble using the Date.FromText function to get this in date value. I am VERY NEW to power query so please describe in baby steps.

Here is my setup now, where [Original date] column contains text strings of 7 or 8 character. With the following custom column formula, I am getting an error in each cell
= Date.FromText( Text.PadStart([Original Date],8,"0"), "MMDDYYYY")

Am I missing something from a syntax point of view? I have seen instructions where I have to break the text string into 3 columns and then recombine, but I'd rather get it all done in one step and I can't imagine that is impossible.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
post a link to shared file (not a picture) with sample of the data
 

dls0406

New Member
Joined
Oct 7, 2019
Messages
15

ADVERTISEMENT

I cannot post the data as it is confidential - not mine to share. However I would assume there must be an easy formula that someone with powerquery expertise can share, to convert an 8-digit text string into a date. The text string, for example, is 03232005 and I want powerquery to recognise it as a date (March 23, 2005, but any format is fine)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I don't want confidential data but representative generic sample (more then one record)
 

dls0406

New Member
Joined
Oct 7, 2019
Messages
15

ADVERTISEMENT

Okay....I cannot give data file, but this is a sample of what the data looks like that I would like to convert to something that is recognized as a date by powerquery.

Original Date (Formatted as Text "ABC")
3232005
6122018
12282009
10012006
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe add prefix 0, cut string to 8 characters from right then Text.Insert(Text.Insert([Original Date],2,"/"),5,"/") and Parse
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I can't check because I am not using us style date format ;)
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,127,707
Messages
5,626,404
Members
416,182
Latest member
elija95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top