Skip to main content

Hey Airtablers,

I need help.

I can’t workout a formula or workaround to determine if a record with a date field is equal to ‘This Week’ (being: this block of Monday to Sunday), and then count a field if the outcome is true.

I know how to see if the record’s date is within the last 7 days:

IF(DATETIME_DIFF(NOW(),Date,'days') < 7,ValueToAdd,"0")

Even to see if it is within the last week on a particular day:

IF(DATETIME_DIFF(NOW(),Date,'days')< 7,IF(DATETIME_FORMAT(Date,'ddd')="Wed",ValueToAdd,"0"),"0")

But still can’t figure out how to make it display the ValueToAdd if it happened between this particular block of Monday – Sunday.

If a record’s date is Friday (for example) and checked on a Friday, the value should report.
But when checked the following Monday, the value should = 0.

Any advice?

Hi

You could use the WeekNum() date function to find the week number - the only slight issue is that in Airtable the week seems to be from Sunday to Saturday (although you could adjust this using WeekDay() to determine whether to add or subtract from the week number as appropriate (I think you just subtract 1 if its a Sunday??). Here’s a simple example:

IF(WEEKNUM(Date) = WEEKNUM(TODAY()), “Same Week”, “Not”)

There is a useful Function Reference you can look at:

Hope this helps.

Julian

Hi

You could use the WeekNum() date function to find the week number - the only slight issue is that in Airtable the week seems to be from Sunday to Saturday (although you could adjust this using WeekDay() to determine whether to add or subtract from the week number as appropriate (I think you just subtract 1 if its a Sunday??). Here’s a simple example:

IF(WEEKNUM(Date) = WEEKNUM(TODAY()), “Same Week”, “Not”)

There is a useful Function Reference you can look at:

Hope this helps.

Julian


Julian,

I’m so thankful for the simplicity of your answer and your generosity in taking the time to answer.
I’ll test first thing tomorrow – I’ll post my results.

Thank you! ^∞

Hi

You could use the WeekNum() date function to find the week number - the only slight issue is that in Airtable the week seems to be from Sunday to Saturday (although you could adjust this using WeekDay() to determine whether to add or subtract from the week number as appropriate (I think you just subtract 1 if its a Sunday??). Here’s a simple example:

IF(WEEKNUM(Date) = WEEKNUM(TODAY()), “Same Week”, “Not”)

There is a useful Function Reference you can look at:

Hope this helps.

Julian


This worked! Thanks so much again :raised_hands:

Hey, this works great for the binary ‘was it this week’, but is there a way to create an IF statement that can determine whether something’s due Today, Tomorrow, This Week, or This Month?

I have this so far, but I’m not having much luck:

IF({Due Date} = BLANK(), "Unknown", 
	IF((DATETIME_DIFF({Due Date}, TODAY(), 'days')<1, "Today",
		IF((DATETIME_DIFF({Due Date}, TODAY(), 'days')<2, "Tomorrow",
			IF(WEEKNUM({Due Date}) = WEEKNUM(TODAY()), "This Week", "Soon"
		  ))
	  ))
  )

Am I on the right track? Does anyone have any advice?

Hi

You could use the WeekNum() date function to find the week number - the only slight issue is that in Airtable the week seems to be from Sunday to Saturday (although you could adjust this using WeekDay() to determine whether to add or subtract from the week number as appropriate (I think you just subtract 1 if its a Sunday??). Here’s a simple example:

IF(WEEKNUM(Date) = WEEKNUM(TODAY()), “Same Week”, “Not”)

There is a useful Function Reference you can look at:

Hope this helps.

Julian


Great little formula, but be aware you need to change from fancy to regular quotation marks, if you copy/paste the formula.

Terms & ConditionsAccessibility statement