Add or Subtract X Number of Days

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!

Workflow

Accepted answers

danilang

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

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

MarqueeCrew

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

All comments

danilang

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

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

MarqueeCrew

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?

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.

RClark_

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703