Due Date is this week or next week forumula

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:

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

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!

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

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.

thanks much simpler than i was trying

IF(
   DATETIME_DIFF(
      {Confirmed Date},
      TODAY(),
      ‘days’
   ) <= 7,
   [your flag here]
)

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!!

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