Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Oct 31, 2020 06:21 PM
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.
Oct 31, 2020 11:20 PM
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"
)
)
)