Creating a new field with a data and adding to that date

[delcarpiosamuel]

Hi, 

Can someone please help me with the following?

I want to add a new field for dates to my data. Dates are not part of my input data. 

I want to be able to have "10/27/2019" for the date field for this week and then next week add 7 days so the field reads "11/3/2019". Additionally, I want the field to be a date not a string. 

I've tried using the formula and the date time tools but I cannot find a way to add the 7 days for next week's data. 

Thank you!

Sam 

[JoshuaGostick]

Hi @delcarpiosamuel,

The DateTime tool is a user-friendly way of converting string data into dates. Once the data is in date format, you can use DateTimeAdd() function to add 7 days to it.

Let me know if you have any questions or need help on this.

Thanks,

Josh

[delcarpiosamuel]

Thank you, Josh!

How would you recommend creating the date field in the formula tool?

Currently, I have ToDate(43765) and Data Type as Date to get 2019-10-27. How can I convert it to 10/27/2019? And is there a better way to input the date field using the formula tool?

Using the formula you provided I was able to get the field to show as 2019-11-03! How can I change it to show 11/03/2019?

Thank you for your help!

[JoshuaGostick · Accepted solution]

Hi @delcarpiosamuel

You could use the DateTime tool again to format the standard yyyy-MM-dd date format to a different format like MM/dd/yyyy.

In this case, configuring the DateTime tool like below should solve your issues.

However, note that Alteryx will treat 10/27/2019 as string data instead of date data.

Josh

[CharlieS · Accepted solution]

To convert a date field to the MM/DD/YYYY format, use the following expression:

DateTimeFormat([Date],"%m/%d/%Y")

This can be incorporated into the formula you already have:

DateTimeFormat(ToDate(42765),"%m/%d/%Y")