I need a formula to give me records with past dates up to 7 days ago. Can you help?

[Marcegon]

I am using the following formula but it gives me last week's records only:

DATETIMEPARSE([End_date], "%Y-%m-%d") >= DATETIMEADD(DATETIMENOW(), -7, "days")

Also, I need a formula that gives me records with past dates and + 14 future dates.  The formula below is not giving me past dates:

DATETIMEPARSE([Start_date], "%Y-%m-%d") >= DATETIMEADD(DATETIMENOW(), +14, "days")

Thank you for your help!

[johnyli168]

Hey @Marcegon ,

You could look to add another "AND" statement to catch the other side of the +/- days. 

I have attached an example with some dummy data.

IF [EXAMPLE DATE] >= DATETIMEADD(DATETIMENOW(), -7, "days") AND [EXAMPLE DATE] <= DATETIMEADD(DATETIMENOW(), +7, "days") THEN "Yes" ELSE "No" ENDIF

[SPetrie · Accepted solution]

Correct me if I'm misunderstanding the ask, but it sounds like you only want dates that are 7 days old or older.

The formula you have is saying you only want dates that come on or after 7 days ago. Saying a date is greater means it comes after the test date. 

You want to switch to <= so that its saying you want dates the came before your test date.

Same with the other formula, switching to <= will give you all dates that are up to 14 days after the current date.

[Marcegon]

Hi, the formula kind of fixed the issue because I am getting past records, but I am getting records beyond (2) weeks as well.  I am not sure what is wrong with the rule (see rule and output below)

IF [Actual format] = "Virtual" OR [Actual venue type] = "Virtual" THEN "Not required" ELSEIF DATETIMEPARSE([Start_date], "%Y-%m-%d") <= DATETIMEADD(DATETIMENOW(), +14, "days") AND IsEmpty([Event Location]) OR [Event Location] = "TBD" THEN "0" Else [Event Location] ENDIF

OUTPUT - I don't want to enter a "0" for a record with a date beyond 14 days.

Start_date	End_date	Fiscal Year	Event Location
9/14/2022 17:00	9/14/2022 19:00	FY22	0

Any help would be much appreciated.  Thanks!

[SPetrie]

Can you supply some example input and expected output for this formula?

The example here has a different date format than what you are parsing from in the formula and since event location is already filled in, there is no way to really tell what the formula was acting upon.

Having a better sample set of input and expected output would be helpful.

If you are able to attach your workflow, that would be even more helpful so we could see what other tools are in play that may affect how the fields are being populated.

[Marcegon]

It worked.  Thank you so much!  It looks like something else was wrong.