community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Count months from today

Asteroid

Hi everyone! I need to count how many days from today passed from the definite date.

 

Definite date

How many days from today (05.11.2018)

01.11.20184
01.10.201835
01.10.2016etc...

 

Thanks for any help

Alteryx Certified Partner
Alteryx Certified Partner

@Nastya,

 

Dates in Alteryx are  ISO format:  YYYY-mm-dd

 

To convert your date to an ISO date:

DateTimeParse([Definite Date],"%m.%d.%Y")

Then to find the difference between the date and a constant date:

DateTimeDiff([Definite Date],"2018-05-11","Days")

All in one expression:

DateTimeDiff(DateTimeParse([Definite Date],"%m.%d.%Y"),"2018-05-11","Days")

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Pulsar
Pulsar

Hey @Nastya!

 

You will first need to make sure that your dates are in Alteryx's preferred format (yyyy-mm-dd). You could do this with a DateTime tool in the Parse category. You could also use the following expression in a Formula tool:

DateTimeParse([Field1],"%d.%m.%Y")

 

Once your field is formatted as a date, use this expression in a Formula tool:

DateTimeDiff(DateTimeToday(),[date],"days")

One option would be to combine the above to expressions in one like so:

DateTimeDiff(DateTimeToday(),DateTimeParse([Field1],"%d.%m.%Y"),"days")

This way, you could just use one Formula following your input data to create your numeric difference field.

 

Hope this helps!

 

EDIT: Eerily similar to @MarqueeCrew

Highlighted
Asteroid

Hm,  do you have any ideas why the output in example is negative and somewhere is positive

 

Definite day Days
2017-03-05 14:00:00-431
Pulsar
Pulsar

Hi @Nastya

 

The number may be negative depending on your order of dates in the formula and where the Definite day falls in relation to today's date. The generic formula is set up like this: DateTimeDiff(dt1,dt2,u) and takes dt1 - dt2.

 

If Definite date is before today and you take DateTimeDiff([Definite date],DateTimeToday(),"days"), you will get a negative number.

If Definite date is before today and you take DateTimeDiff(DateTimeToday(),[Definite date],"days"), you will get a positive number.

 

On the other hand, if Definite date is after today and you take DateTimeDiff([Definite date],DateTimeToday(),"days"), you will get a positive number.

If Definite date is after today and you take DateTimeDiff(DateTimeToday(),[Definite date],"days"), you will get a negative number.

Labels