Skip to main content

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.

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"

        )

    )

)

Reply


Terms & Conditions