community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Change year in string date

Highlighted
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

Pulsar
Pulsar

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!

Meteor

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

 

Larry

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
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