Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

add cardinal to date

Josef1
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"

 

6 REPLIES 6
Maskell_Rascal
13 - Pulsar

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

 

RolandSchubert
16 - Nebula
16 - Nebula

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
19 - Altair
19 - Altair

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

fmvizcaino
17 - Castor
17 - Castor

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

 

Josef1
7 - Meteor

perfect 

Josef1
7 - Meteor

thanks

Labels