Number of weeks formula?

Hi everyone! I have a table with several courses, and event dates for each course. I’m trying to assign the week of the course each event falls in. So, if Course A starts 11/1/20200 and ends 12/31/2020, all events between 11/1 and 11/7 would be assigned Week 1; all events between 11/8 and 11/14 would be assigned Week 2 (and so on) until 12/31/2020.

Is there something in Airtable that can do that? Currently I’m writing a formula (If course = X and date is after 11/1/2020 and before 11/8/2020, then Week 1; if course = X and date is after 11/8/2020 and before 11/15/2020, then Week 2…etc, etc)

Any help on how to make this simpler would be appreciated!
Thanks!
Jeffrey

Hi @Jeffrey_Brandt - try this: based on this table:

the Event Week formula is:

'Week ' & (DATETIME_DIFF({Event Date}, {Course Start}, 'weeks') + 1)

The main part of the formula uses DATETIME_DIFF and just gets the difference in weeks of the two dates (adding 1 to the result to get the right week number value).

=======================

Want to learn Airtable Scripting? 1 day bootcamp coming soon!

=======================

Hi Jeffrey,

these two articles helped me and might be what you’re looking for:

Hi Jonathan,

That works perfectly! Thanks so much!

Jeffrey

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.