I’m trying to come up with a formula to denote if something is due “This Week” or “Next Week” but can’t seem to nail it down. I’ve tried using this:
IF((WEEKNUM({Due Date}, IS_SAME(WEEKNUM(TODAY()))))
Airtable accepts the formula, but I get an #ERROR in the field. I’m just looking for it simply to return a “1” or “0”. I also need to denote Next Week in another field, which I was planning to build off this formula if I got it working. Any help is appreciated.
Hi there! I’m no expert, but I think maybe you’re returning an error because the function “IS_SAME()” is trying to compare two dates, while “WEEKNUM()” is returning only numbers.
If you change “IS_SAME()” to an “=” sign, it should it work.
IF(WEEKNUM({Due Date}) = WEEKNUM(TODAY()), 1, 0)
Hi there! I’m no expert, but I think maybe you’re returning an error because the function “IS_SAME()” is trying to compare two dates, while “WEEKNUM()” is returning only numbers.
If you change “IS_SAME()” to an “=” sign, it should it work.
IF(WEEKNUM({Due Date}) = WEEKNUM(TODAY()), 1, 0)
Appreciate it. Got it working now.
FYI, if you are only comparing using WEEKNUM, and you have records that may eventually span over multiple years (past or future), this formula will end up buggy because you’re not taking years into account. For example, we’re currently in week 44 of 2018, but if you had records with dates of week 44 in 2017 or 2019, they will also be incorrectly marked a 1 instead of a 0. I’d recommend comparing week year and week numbers combined together, I think more robust formulas would be:
DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(TODAY(),'GGGG-WW')DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW')OR(DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(TODAY(),'GGGG-WW'), DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW'))Awesome, and excellent point. Luckily we’ll just be using the records for a month and be done with them. Definitely will keep this in mind though, thanks!
FYI, if you are only comparing using WEEKNUM, and you have records that may eventually span over multiple years (past or future), this formula will end up buggy because you’re not taking years into account. For example, we’re currently in week 44 of 2018, but if you had records with dates of week 44 in 2017 or 2019, they will also be incorrectly marked a 1 instead of a 0. I’d recommend comparing week year and week numbers combined together, I think more robust formulas would be:
DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(TODAY(),'GGGG-WW')DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW')OR(DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(TODAY(),'GGGG-WW'), DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW'))I think I’ve found a “Sunday night” (or localization?) bug in Airtable’s date functions…
At 10:38 PM PST on 2019-01-06:
DATETIME_FORMAT(“2019-01-06”,‘GGGG-WW’) returns “2019-01”
DATETIME_FORMAT(TODAY(),‘GGGG-WW’) returns “2019-02”
Hopefully, the functions will work as expected tomorrow.
I think I’ve found a “Sunday night” (or localization?) bug in Airtable’s date functions…
At 10:38 PM PST on 2019-01-06:
DATETIME_FORMAT(“2019-01-06”,‘GGGG-WW’) returns “2019-01”
DATETIME_FORMAT(TODAY(),‘GGGG-WW’) returns “2019-02”
Hopefully, the functions will work as expected tomorrow.
You’ve run into a situation where one of your calculations is using GMT and the other isn’t. If you wrap the dates in SET_TIMEZONE(), both formulas will report based on your local date:
DATETIME_FORMAT(SET_TIMEZONE('2019-01-06','America/Los_Angeles'),'GGGG-WW')
DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/Los_Angeles'),'GGGG-WW')
You can find a list of valid location identifiers here.
I am reading this but struggling, I am new to airtable I have a field called “confirmed date”
I want to flag if this column was > than 1 week ago but am struggling to get this combination above to work.
Any help would be appreciated
I am reading this but struggling, I am new to airtable I have a field called “confirmed date”
I want to flag if this column was > than 1 week ago but am struggling to get this combination above to work.
Any help would be appreciated
IF(
DATETIME_DIFF(
{Confirmed Date},
TODAY(),
‘days’
) <= 7,
ryour flag here]
)
That should work. I might have my signs backwards, in which case you’d just switch it to >= 7.
thanks much simpler than i was trying
IF(
DATETIME_DIFF(
{Confirmed Date},
TODAY(),
‘days’
) <= 7,
your flag here]
)
That should work. I might have my signs backwards, in which case you’d just switch it to >= 7.
I took your spaces out ( is that correct)
& changed my description but it is saying formula incorrect
IF(DATETIME_DIFF({Date Next Visit Confirmed},TODAY(),‘days’)<=7,“check”)
Kind Regards,
Howard Wood
I took your spaces out ( is that correct)
& changed my description but it is saying formula incorrect
IF(DATETIME_DIFF({Date Next Visit Confirmed},TODAY(),‘days’)<=7,“check”)
Kind Regards,
Howard Wood
wont reply by email again!!
I took your spaces out ( is that correct)
& changed my description but it is saying formula incorrect
IF(DATETIME_DIFF({Date Next Visit Confirmed},TODAY(),‘days’)<=7,“check”)
Kind Regards,
Howard Wood
Spacing doesn’t matter in Airtable’s formula builder.
Looking at that again, though, I think it needs to be ‘<= -7’ rather than positive 7.
And regarding the error, try manually typing the formula into the formula builder — it may be a problem with “smart quotes”
TOOK A BIT OF EXPERIMENTING BUT FINALLY MANAGED IT THANKS
tHIS WORKS
IF((DATETIME_DIFF({Date Next Visit Confirmed},TODAY(),‘days’))<-7,“CHECK”,“OK”)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.