DateTimeToday() 2020-05-18

[Pascal_R]

Hi all,

Is there a frormula or a way to have the date format like 18.05.2020 instead of 2020-05-18?

Thank you for your help.

Pascal

[JosephSerpis]

Hi @Pascal_R I mocked up a workflow you can use the dateformat function to format a date to whatever you need it to be however it will have to be a string field in alteryx as it only recognises dates in the format YYYY-MM-DD as a true date format. 

[Pascal_R]

Thank you JosephSerpis 

For calculate the difference between Dates I have to use the  format YYYY-MM-DD as string field?

I like to calculate the "expire date - actual date" = difference in days...

Regards,

Pascal

[RolandSchubert]

Hi @Pascal_R ,

to calculate the difference between dates you have to convert the date to a Date (or Datetime) datatype using DateTimeParse([DateField], '%d.%m.%Y') if your date has the format dd.mm.yyyy. The function DateTimeDiff([LastDate], [FirstDate], 'days') will return the difference in days.

Let me know if it works for you.

Best,

Roland

[Pascal_R]

Hi RolandSchubert,

I converted now the actual date and the SLED/BBD to Datetime but as you can see on the screenshot there is something not working with "days" on the difference formula... do you see what Needs to be changed?

Regards,

Pascal

[JosephSerpis]

Hi @Pascal_R 

Your actual date is not in a datetime format alteryx need its to be in YYYY-MM-DD HH:MM:SS if you use datetimenow() those formulas should work so long as SLED/BBD is in the correct format. Datetimediff needs the dates to be in the correct format in alteyrx to work I have mocked up a workflow to showcase this.

[Pascal_R]

Thank you so much JosephSerpis!

Waht I can see is that the SLED/BDD is  2024-04-01 in the Input file therfore I guess I sill have blanks (see screenshots)

Do I Need to create an attional formula above your ones that ts the same format?

Regards,

Pascal

[JosephSerpis]

Hi @Pascal_R I revised the workflow what I would say is ensure your dates are in the correct date format in alteryx as this is the only way datetime functions work correctly hence why you are seeing blanks. Would it be possible to provide a sample of your date or even mock data with your workflow ?  

[Pascal_R]

Thank you again JosephSerpis

I have now an ouput but the numbes are not negativ, please see attached the formula I have in Excel vs. the Output out of Alteryx.

Somehow we need to tweek that as well right?

Regards,

Pascal

[JosephSerpis]

Hi @Pascal_R I produced a workflow that produces the output in your excel.