# Re: Is "set date' 7 days in the past or future?

1156 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I can sort of see my mistake but not sure how to come at it a different way to correct it. What i am trying to ask is “Is the job booked in date, within the previous 7 days of today ? Is the Job booked in Today? Is the Job booked in within the next 7 days from today?”

What i have thus far:

``````IF({Job Booked In} >= 7, "PREVIOUSLY",
IF({Job Booked In} <= 7, "UPCOMING",
IF({Job Booked In} = TODAY(), "TODAY"
)))
``````

“Upcoming” and “Today” both work but “Previously” does not. I’m guessing it has to do with it being a negative number i am not sure what i am looking for.

18 - Pluto

Comparing a date against a literal number isn’t the correct way to check for date differences. You’ll need to use the `DATETIME_DIFF()` function. Because you’ll need to make this comparison multiple times, I recommend making a separate formula field that just calculates the difference (I’ll call this field `{Difference}` to make things easy):

``````DATETIME_DIFF({Job Booked In}, TODAY(), "days")
``````

Negative values mean the `{Job Booked In}` date is before the current date, while positive numbers indicate dates in the future. With that done, your main formula could look like this:

``````IF(
Difference = 0, "TODAY",
IF(
AND(Difference >= -7, Difference < 0),
"PREVIOUSLY",
IF(
AND(Difference > 0, Difference <= 7),
"UPCOMING"
)
)
)
``````