Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert date format in bank statement

cterrence
8 - Asteroid

Hi 

I have a bank statement in text format and I am unable to convert the transaction date  into dd/mm/yyyy. I have used Dateformat tool or formula, but it just does not

 

 

Attached is the workflow 

 

Please assist

 

Thanks

CT

7 REPLIES 7
Yoshiro_Fujimori
15 - Aurora

@cterrence , Could you also attach the input files (Bendigo.txt & Sites.xlsx)?

Yoshiro_Fujimori
15 - Aurora

Not knowing the original date format, the date-time functions should work.

 

If your original format is YYYYMMDD in String type,

you may first convert it to Date with DateTimeParse function,

and then convert to your favorite format (in this case dd/mm/yyyy) with DateTimeFormat function.

 

DateTimeFormat(

  DateTimeParse([Input],"%Y%m%d"),

  "%d/%m/%Y")

 

If you have different date format in the input, you need to change the format param in DateTimeParse function.

For details of datetime functions, check the help page. https://help.alteryx.com/20223/designer/datetime-functions

 

If you still have any issue, please attach the input data of the date.

cterrence
8 - Asteroid

Capture.JPG

 

Thank you very much for your reply. It doesnt work

cterrence
8 - Asteroid

Hi - I have attached the text file in my first post (B.txt).  I have split the date from  - "TRAN EFF DATE 230228"

Yoshiro_Fujimori
15 - Aurora

@cterrence ,

 

Thanks for the data.

When I run the workflow to convert "230228" with "%y%m%d" format, I got warnings as below:

Formula (10) DATETIMEPARSE:

Cannot convert "230228" to a date/time with format "%y%m%d" and language "English":

Month number is out of range 1..12: '28'

 

The formula should be correct, but in some reason Alteryx Designer seems to confuse "28" as "month". (Maybe a bug, I don't know.)

Anyway, as a workaround, I added "20" at the head of the 6 digits to make it "20230228" and convert it with "%Y%m%d".

Then it worked.

 

NG Case = DateTimeParse([Date_yymmdd],"%y%m%d")

OK Case = DateTimeParse("20" + [Date_yymmdd],"%Y%m%d")

Transaction Date = DateTimeFormat([OK Case],"%d/%m/%Y")

 

Output

Yoshiro_Fujimori_0-1678072815771.png

 

Does it work for you?

Yoshiro_Fujimori
15 - Aurora

I found a description in the help page as below. I guess this is the case you are facing...

 

https://help.alteryx.com/20223/designer/datetime-functions#specifiers

Limitation with 6-Digit Dates
Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can...

Use 4 digits for the year (for example, 2017 instead of 17), depending on your range of dates.
Use the RegEx tool to insert a space after the first 2 digits in the string.

 

So it is not a bug but a known limitation.

 

cterrence
8 - Asteroid

Thank you very much Yoshiro san. 

Labels