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
Solved! Go to Solution.
Here is an expression that will pad the the zero as needed:
PadLeft(ToString(ToNumber(Right([Quarter],1))*3), 2, '0')+','+Left([Quarter],4)
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
Thanks Joe!