cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## 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.2018 4 01.10.2018 35 01.10.2016 etc...

Thanks for any help

Alteryx Certified Partner

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

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

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

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