Start a new topic

Regular Expression: Format Artez Date Fields

I have seen lots of examples of interesting date formats, and was recently reminded of a particularly odd specimen. 

If you have ever tried to import something from Artez with a date field, you may have noticed that the dates tend to look a little strange! For example, a date that should be 05/26/2015 will end up in your import file like 2015-05-26 04:18:06:007, which to say the very least, Raiser's Edge will not accept!

 

Lucky for you, there is a Regular Expression for that:

 

Replacement Value: 

$2/$3/$1

 

Value to Match On: 

 

(^\d{4})-((?<=-)\d{2}(?=-))-(\d{2})\S*

 

image


ARTEZDATES.xlsx

Please note that this formula is specific to Artez date formats. Thanks and keep up the good work!

Allison B.

 


Yeesh - looks like an ISO 8601 without the time zone specified. (https://en.wikipedia.org/wiki/ISO_8601)

 

Great example of "Regex to the rescue" though!

Hi Alison,

 

I was wondering if you'd be able to help me with a regex to remove a time stamp and reformat the date.

 

The current format is

YYYY:MM:DD hh:mm:ss

 

and I would like it to be DD/MM/YYYY

 

Are you able to help?

Hello Deborah,

 

That was a fun little challenge for me! After a bit of trial and error, I think I finally have just the RegEx solution for you. This will be a new dictionary you can create by simply copying and pasting from this excel file: Dcty4Deb.xlsx

 

Screenshot of how the dictionary should look:

image

 

Hope this helps you! Thanks and keep up the good work!

 

Allison B.

Thank you so much Allison! This works a treat and will save me and my team from having to adjust this column every time!

 

Thanks!

Hi,


I have attempted to resolve the below based on comments in this topic, but no luck. May I ask how to change 2020-05-01T01:42:24 to DD/MM/YYY?


Thanks in advance.


Todd

Login or Signup to post a comment