Alteryx Designer Discussions

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

Add or Subtract X Number of Days

robertfishel
8 - Asteroid

Hello Alteryx fans, and Happy Friday!

 

I have been unable to find a post that succinctly states how to both add x number of days to today's date, or subtract x number of days from today's date.  For example, I am working on a  workflow that has a step to filter out records that are older than 90 days from a date column.  So would the best way to accomplish this be to use a Filter Tool, and then add a formula?  What should the formula be?  Thanks! 

8 REPLIES 8
danilang
18 - Pollux
18 - Pollux

The formula in the Filter tool should be like

 

[Datefield]<DatetimeAdd(ToDate(DatetimeNow()),-90,"days")

DateTimeNow() returns the current datetime.  ToDate strips off the time portion and DateTimeAdd(xxx,-90,"days") subtracts 90 days from this.

 

 

 

Dan

danrh
13 - Pulsar

Adding to @danilang's answer, you can add DateTimeToday() rather than ToDate(DateTimeNow()).

MarqueeCrew
19 - Altair
19 - Altair
I'd use DateTimeStart() as the single source of the date. It is the start of the job. Now as a caution, if you datetimetrim(DateTimeStart(),"day") you know that the time is removed. When you compare DateTime fields sometimes it matters.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
robertfishel
8 - Asteroid

Thanks to all for your help.  I'm still not getting the filter to work correctly though.  Should I be using another step before my filter?  Something to condition my date?

robertfishel
8 - Asteroid

Got the filter to work by doing the following:

 

Formula tool (added an Output Column called DateDiff) with a formula like this:

ToNumber(DateTimeDiff(DateTimeParse([Date],"%m/%d/%Y"),DateTimeToday(),"days"))

 

Then a Select tool to change the type to Double for the DateDiff column

 

Then a Filter tool with the formula [DateDiff] < -90

 

TheHMD
5 - Atom

I had to figure that one out, too, only minutes ago. With the Filter tool on your sheet and selected, look in the Functions tab in the left pane for the set of "DateTime" functions. Expand the "+" symbol and click on DateTimeAdd(dt,i,u). In the one-line box below it, you'll see an example of its use.

 

In my case (as in yours) I needed to subtract from today's date (aka DateTimeToday()).

Suppose you want to filter your data to restrict to where the [SalesDate] field is more recent than 7 days old.

 

In the Expression area, type this:

[SalesDate] > DateTimeAdd(DateTimeToday(), -7, "days")

 

or filter to get only rows where [ProposalDate] more than a month away:

[ProposalDate] > DateTimeAdd(DateTimeToday(), 1, "months")

 

 

Jason_palacios
5 - Atom

Hello dear all,

 

Hope you are well!

 

I was wondering, what if i need to add 2 or more X numbers of days to a date. I'm actually working on a workflor which calculate the terms of payments to our suppliers, those payments terms have differents number of days (example 90, 180, 60) and i need to sum those numbers of days to my date. The formula that you give us to know is perfect but only process a singular number of day like in the sample, which only process "-90"

[Datefield]<DatetimeAdd(ToDate(DatetimeNow()),-90,"days")

but what if i need to process multiple variables number of days, is there some way to process it.

 

I hope articulate myself as a good way.

 

Thanks in advance for the comments.

Panchnachi
5 - Atom

I am looking out for a similar solution, but haven't got one yet!

 

Currently the solution provided gives an output of one field, if I will need multiple dates for example

 

DateTimeAdd(DateTimeToday(),+1,"days") will provide an output of 07/09/2020

 

Is there a way I can get a series of dates like 07/09/2020 07/10/2020 07/11/2020?

Labels