Start a new topic

Importing a date with a year more on it

Hi there,

I wonder if it's possible to do that using regex. I have a date field on my import file which I'd like to be imported as it is plus one year.

For example, 24/10/2018 should be 24/10/2019. 

Is that possible? And if so, how?

Many thanks,


Hi Grazi,

I've attached a screen shot of one way of doing this.  For each line it is looking for a date that ends in a year (2017, 2018, etc..).  It then replaces that year with the next year with an @ symbol at the end (this is a random character that I selected, it doesn't really matter what is used).  Then at the very end I remove the @ symbol.  This one is built out to 2021, but you can build it out as far back or forward as you want using this logic.


Thanks John, that's amazing! It worked perfectly! Thank you so much! 

Sorry, a quick question - can I do that and remove the time-stamp? Thank you! :-)  

Yes, but it will require a slightly different entry to remove the time stamp then what you'd typically used.  Since we are already using the replacement value of -BLANK- at the bottom we can't use it at the top to remove the time stamp, so we'll just break the date out into sections and only use the portions that we need.  On the very first line of the dictionary use value to match on ^(\d{1,2})/(\d{1,2})/(\d{4}).* and replacement value $1/$2/$3  Everything else will be the same after.

Hi John,

I'm not sure I understand... I've done the below, but it didn't work (it even didn't replace the year anymore :-(... Thanks!

$1/$2/$3 ^(\d{1,2})/(\d{1,2})/(\d{4}).*  TRUE FALSE
2018@ 2017$ TRUE FALSE
2019@ 2018$ TRUE FALSE
2020@ 2019$ TRUE FALSE
2021@ 2020$ TRUE FALSE
2022@ 2021$ TRUE FALSE
2023@ 2022$ TRUE FALSE
2024@ 2023$ TRUE FALSE
2025@ 2024$ TRUE FALSE
2026@ 2025$ TRUE FALSE
2027@ 2026$ TRUE FALSE
2028@ 2027$ TRUE FALSE
2029@ 2028$ TRUE FALSE

Double check to make sure your values to match on and replacement values don't have any extra spaces at the end.  That can throw things off.

Genius, thanks John! :-)

Login or Signup to post a comment