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

Change year in string date

lahughes
7 - Meteor

I have data that contains dates in string format roughly 75,000 lines of data.

 

The date is in the following sting format: 06/01/2018.

 

Some of the dates though have mis-types in the year for example - I may see 06/01/3018.  The date should really be 06/01/2018.

 

I have currently stripped the year and then summarize the result to get all of the possible years in the data, that may be things like the below:

2016

2017

2018

2048

3018

 

That shows me what the years are in the data and I can spot issues.....

 

But I am struggling with figuring out how to then change the year of say 3018, to 2018 instead for a given entry, while still keeping the month and day that the original entry has.

So:

06/01/3018 would change to 06/01/2018

11/16/3018 would change to 11/16/2018

 

Sure there is a very simple way I am just not thinking about.

 

Thanks,

Larry

4 REPLIES 4
Kenda
16 - Nebula
16 - Nebula

Hey @lahughes!

 

Are you familiar with the replace function? This is what I would recommend using here. 

 

In your Formula tool, try this expression:

replace([Field1],"3018","2018")

This says to replace "3018" with "2018" in Field1. You can modify the strings in quotes for each of the years you need to change.

 

Hope this helps!

lahughes
7 - Meteor

Thank you.  Forgot about the Replace function.  Appreciate the help and quick reply!!

 

Larry

MarqueeCrew
20 - Arcturus
20 - Arcturus

@lahughes,

 

A fancy way to do this replacement to allow for more error corrections....

 

regex_replace([date],"(\d+\/\d+)\/3(\d+)",'$1/2$2')

but to simply ensure that the year always starts with a "2", I'd just use:

 

left([date],6)+"2"+right([date],3)

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
lahughes
7 - Meteor

Thanks Mark.  I need to continue to skill up on the Regex tool and your suggestions will help with that.  The Replace function gave me a simple and quick way to give me the results I was looking for and I can use that to compare my results from the Regex side to see if I am getting that syntax correct.  Thanks for the suggestions.

 

Larry

Labels