Add or Subtract X Number of Days

[robertfishel]

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! 

[danilang · Accepted solution]

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 · Accepted solution]

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

[MarqueeCrew · Accepted solution]

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

[robertfishel]

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]

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]

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]

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]

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?

[RClark_]

DateTimeAdd({Date to modify}, {Number of days to add (negative to subtract)}, "days")