Date format

Question

Hi,

I need a bit of help!

I'm analyzing payment cards with their expiry dates. For some of them those exp dates come in the format ie. Jun-23, some 06/23. I'd like to convert any dates in the format 06/23 to Jun-23.

Example below

Expiry Date New Format

Jun-23          Jun-23

06/23            Jun-23

May-24          May-24

03/25             Mar-25

Any help will be much appreciated.

alexnajm · Accepted Answer

This formula works for the data provided: IF Contains([Field1], "/") THEN DateTimeFormat(DateTimeParse([Field1],"%m/%y"),"%b-%y") ELSE [Field1] ENDIF

Pilsner · Accepted Answer

Hello @fieldew1

To tackle this task, I would begin by identifying which records already match your desired format. The regex match function inside a formula tool can do this nicely. As you can see, I have specified that we are looking for 3 letters "\w{3}" followed by a dash "\-" followed by two numbers "\d{2}".

Any records that match this format are already complete so we can simply set the new column equal to the old column:

Once you have done this, we need to consider the records that didn't match (those found in the false anchor of the filter tool). To handle these I think the easiest method is to first convert them into the data time format, using the specifiers MM/yy.

Then I would convert them from the date format into your desired format using another date time tool with the specifiers Mon-yy

By dropping unnecessary columns and unioning the results back together you get the following output.

I've attached the workflow with annotations below to try and assist further.

Please let me know how you get on.

Regards - Pilsner