Skip to main content

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 @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 Jonathan,


That works perfectly! Thanks so much!


Jeffrey

Reply


Terms & Conditions