Data addition and Converting

Question

Hi community,

I would like to ask how we build the workflow if we have the data as per column A screenshot below:

1. Firstly, i have multiple of dates whereby i plan to add additional one month on this date then it will present the result as per screenshot below ( column C). May i know can we do it in Alteryx?

2. Secondly, after we obtain the final date, i would like to convert it to the format  Y- M- D as per the screenshot below (colum D). But i have three type of date format in this case which is Y-D-M, Y-M-D and D/M/Y. May i know how can i build the workflow so that it can convert it to the normal time setting with Y- M- D.

Many thanks for your help.

Data addition and converting.xlsx

pedrodrfaria · Answer

@SH_94

Your questions are answered within the Formula Tool

To add a month to a date look at the second function using the DateTimeAdd function.

To understand how Alteryx can read and update different date formats look at the first function using the IF logic as well as a DataTimeParse and a Regex Match.

Regex will allow you to read multiple formats (you have to set up the logic as I did below to read the different formats the date column could come in. In this instance it was either the correct Alteryx format (YYYY-MM-DD) or DD/MM/YYY that I was able to identify with the DateTimeParse([Invoice Date],'%d/%M/%y') logic.

Regex is not something I can show you and you will learn right away, you need to understand it first. I added a couple of resources so you can study it up.

https://help.alteryx.com/current/designer/datetime-functions

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20Data

SH_94 · Answer

Hi @pedrodrfaria ,

Thank you for your prompt response.

The result is different with the original table. I have two queries which would like to clarify with you:

1. If currently we does not have the column for "Addition of month", but i want the invoice date to be added with one month to get the final date. May i know how should i build this workflow in this case?

2. For the first and second row of invoice date, i would like to change the format to Y-M-D as the original data show the format of Y-D-M . However, the format for the fourth row is correct whereby it is Y-M-D. But would Alteryx able to differentiate between first ,second and forth row ? As i have multiple of dates which consist of Y-M-D and Y-D- M. Do you know how to fix the format issues?

I am re-uploading the excel file for your reference as i just update the row number 4.

pedrodrfaria · Answer

Hi @SH_94

I'm not sure I understand everything you are asking for. But i attached an example of date conversion and adding dates by one month.

Data addition and Converting.yxmd