add cardinal to date

[Josef1]

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"

[Maskell_Rascal]

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. 

[RolandSchubert]

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

[danilang · Accepted solution]

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

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.

Dan

[fmvizcaino]

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.

Best,

Fernando Vizcaino

[Josef1]

perfect 

[Josef1]

thanks