Date + Days = Future Date

[Anthony_Tonkin]

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.

[Justin_Barrett]

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"
        )
    )
)