Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

add cardinal to date

Highlighted
7 - Meteor

I have a set of dates in the format "Wednesday, 15 April, 2020" .

How can i add the cardinal abreviation to the number of month, ie "st,nd or rd", creating "Wednesday, 15th April, 2020"

 

Highlighted
9 - Comet

Since the date you are using is classified as a String by Alteryx, you can use a Find Replace to add in the cardinal. You will have to first split your data using a Text to Columns to make sure that it isn't turning the year 2020 to 20th20th or something weird like that. I've attached a workflow that should work. 

Maskell_Rascal_0-1586962590027.png

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @Josef1 ,

 

you could convert the date to a date data type and use DateTimeFormat including the cardinal. To assign the right abbreviation, a condition within a formula can be used. See attached sample workflow. Let me know, if it works for you.

 

Best,

 

Roland

Highlighted
17 - Castor
17 - Castor

Hi @Josef1 

 

You can do this by using a Regex parse to split the string and then a formula tool to add the cardinal and then put the pieces back together

danilang_0-1586962792928.png

The formula used here is 

if Contains([RegExOut2],"12") Or contains ([Field1],"11") or contains ([Field1],"13") then
	[RegExOut2]+"th"
elseif EndsWith([RegExOut2], "1") then
	[RegExOut2]+"st"
elseif EndsWith([RegExOut2], "2") then
	[RegExOut2]+"nd"
elseif EndsWith([RegExOut2], "3") then
	[RegExOut2]+"rd"
else
	[RegExOut2]+"th"
endif

 

The first clause is used to handle the special case of  11, 12, and 13.

 

danilang_1-1586962937645.png

 

 

Dan

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @Josef1 ,

 

I'm not sure if there is an easier way to do it, but I'm sharing my solution for you to take a look.

fmvizcaino_0-1586963024868.png

 

Best,

Fernando Vizcaino

 

Highlighted
7 - Meteor

perfect 

Highlighted
7 - Meteor

thanks

Labels