Due Date is this week or next week forumula


#1

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.


#2

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)


#3

Appreciate it. Got it working now.


#4

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:

  • 1 if current week:
    DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(TODAY(),'GGGG-WW')
  • 1 if next week:
    DATETIME_FORMAT({Date},'GGGG-WW') = DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW')
  • 1 if current week OR next week
    OR(DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(TODAY(),'GGGG-WW'), DATETIME_FORMAT({Date},'GGGG-WW')=DATETIME_FORMAT(DATEADD(TODAY(),1,'week'),'GGGG-WW'))

#5

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!