Help with RGEX and Creating Date Time

[dshaw]

I need help with creating a REGEX formula.  I want to take  a column called Quarter in the following string format 2008Q1 and I want to change to the following format date format (Month, yyyy), so 03, 2008 for 2008Q1 so that I can use the DateTime format to convert to DateTime.  So, 2008Q2, would be 06, 2008, 2008Q3 would be 09, 2008, and  2008Q4 would be 12,2008.

Thanks,

Derreck

[Rohit_Bajaj]

Hi Dshaw, Please try the below in a formula transformation. TOSTRING(4*TONUMBER(SUBSTRING([Field1],5)))+','+ SUBSTRING([Field1],0,4) Thanks, Rohit Bajaj

[Rohit_Bajaj]

TOSTRING(3*TONUMBER(SUBSTRING([Field1],5)))+','+ SUBSTRING([Field1],0,4). Corrected the multiplication factor.

[GavinAttard · Accepted solution]

Hi

Rather than use Regex, you can use the Text to Coloumn tool using Q as a delimiter. 

Then hook up to a lookup table to produce month value and build the new field. 

Workflow Attached. 

[Joe_Mako]

Here is an expression that will pad the the zero as needed:

PadLeft(ToString(ToNumber(Right([Quarter],1))*3), 2, '0')+','+Left([Quarter],4)

[dshaw]

Hi Gavin, thank you so much.  You had a similar solution to another community poster.  You are guys are some awesome.  I had to change your look up table slightly and also had to add an extra space between Month and Year in order to the DateTime to work.  Anyway, you got me where I needed to be.

Also, here is the formula that another community poster gave me for use after the using Text to Columns and the "Q" as the delimiter.   His solution did not involve a lookup table but I appreciate both solutions as I am still learning every single day.  Here is the other formula:

datetimeparse([QuarterPart1] +','+ToString(tonumber([QuarterPart2])*3, 0) , '%Y,%m')

Again, thanks.

Derreck

[dshaw]

Thanks Joe!