Due Date is this week or next week forumula

[Adam_Zeis]

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.

[AlliAlosa]

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)

[Adam_Zeis]

Appreciate it. Got it working now.

[drassi]

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

[Adam_Zeis]

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!

[Donald_Newlands]

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.

[W_Vann_Hall]

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.

[Howard_Wood]

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

[Jeremy_Oglesby]

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.

[Howard_Wood]

thanks much simpler than i was trying