Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

REPLACING THE YEAR IN DATE COLUMN

dunkindonut7777
8 - Asteroid

Hi I would like to ask about regarding the date column of my data. I I have here series of dates that has 2020 and 2030 year, I would like to replace the 2030 into 2020 same as the other fields.

 

EffectiveDate
3/30/2030
3/30/2030
4/29/2030
4/29/2030
3/10/2020
3/10/2020

 

 

Can you help me this one pls?

10 REPLIES 10
Qiu
20 - Arcturus
20 - Arcturus

@dunkindonut7777 
Something like this should help./

Capture12.PNG

AngelosPachis
16 - Nebula

Hi @dunkindonut7777 ,

 

Similar to @Qiu solution, but I did not replace the "/" before the year so it keeps the mm/dd/yyyy format.

 

AngelosPachis_0-1607584910814.png

Also if you then want to convert the field to a date field, then that should have a "yyyy-mm-dd" format. You can convert it within the formula tool with the Datetimeparse function.

 

Hope that helps,

 

Cheers

 

Angelos

 

 

grazitti_sapna
17 - Castor

Hi @dunkindonut7777 , My solution is somewhat similar to @AngelosPachis  but using a different function. Please refer to the screenshot and workflow attached.

grazitti_sapna_0-1607586092133.png

 

Kindly mark this post as solution if it works for you.

 

Thanks.

Sapna Gupta
Qiu
20 - Arcturus
20 - Arcturus

@dunkindonut7777 

@AngelosPachis Thank you for pointing out. Sorry.

12Capture.PNG

dunkindonut7777
8 - Asteroid

How about my date is already in date format. Is formula tool applicable for that? 🙂

dunkindonut7777
8 - Asteroid

What if the field is already in date format? Is the formula tool applicable for that? 🙂

AngelosPachis
16 - Nebula

Hi @dunkindonut7777 

 

If your field has a date data type, then the formula provided won't work. You would have to use embedded Datetime Functions within Alteryx to get the answer right.

 

For example, you can do something like this

 

AngelosPachis_0-1607623065265.png

 

Which will subtract 10 years if the year of the effective date is 2030.

 

Hope that helps.

 

Angelos

 

DaveG111
5 - Atom

Hi,

 

How would this differ if you had various years at the beginning? 

For example if you have 2025, 2026 and 2030 and you wanted to change them all to 2020? 

I have a list of a wide range of dates and really want them to change to 2020.

Any help, greatly appreciated.

 

Thanks, 

 

 

LoadedDice
5 - Atom

Old topic but it is possible to change the year even if the field is in date format.

simple formula

 

Replace(ToString([EffectiveDate]), "2030", "2020")

 

If you have multiple years:

 

"2020" + Substring(ToString([EffectiveDate]),4)

 

LoadedDice_0-1661411919491.png

 

Labels