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?
Solved! Go to Solution.
@dunkindonut7777
Something like this should help./
Hi @dunkindonut7777 ,
Similar to @Qiu solution, but I did not replace the "/" before the year so it keeps the mm/dd/yyyy format.
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
Hi @dunkindonut7777 , My solution is somewhat similar to @AngelosPachis but using a different function. Please refer to the screenshot and workflow attached.
Kindly mark this post as solution if it works for you.
Thanks.
How about my date is already in date format. Is formula tool applicable for that? 🙂
What if the field is already in date format? Is the formula tool applicable for that? 🙂
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
Which will subtract 10 years if the year of the effective date is 2030.
Hope that helps.
Angelos
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,
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)