Hello and happy Tuesday,

 

I have been racking my brain trying to come up with a regular expression and wanted to throw this out there in case anyone has any suggestions (I'm looking at you Wayne!!!). I need to take a UK date such as 31/12/2014 and calculate the same date 4 years prior to get 31/12/2010. Basically we just want to subtract 4 from the yyyy in the dd/mm/yyyy date field. I haven't had much luck with mathematical functions within regular expressions in the past, but was hoping someone here has been successful and can offer guidance.

 

*BONUS POINTS if you can also provide me with a regex for the exact opposite, which will add 4 years to the yyyy in a date field instead of subtracting 4 years.

 

Many thanks!

 

Allison B.

Unfortunately regex doesn't do dates OR math :(

My suggestion if you REALLY have to do it in a dictionary is to make a table of all of the years and their corresponding +/- 4 year counterparts

Basically you would have to make a "pair" for each year you want to convert which could be unwieldly if you are looking at birthdays but possible if looking at graduation dates (since there are a much smaller number of years)

ex.
Replacement: $1/2004
Match: (\d{2}/\d{2})/2008

Otherwise, obviously, a custom function would be the best way to go for this. You could make it a global function if it needed to be reused in different profiles.

Thank you Wayne! It seemed like an impossibility, but I had to be sure!

You rock!

Allison B.