Change year in string date

[lahughes]

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

[Kenda · Accepted solution]

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]

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

Larry

[MarqueeCrew · Accepted solution]

@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

[lahughes]

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