The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Jun 01, 2021 08:56 AM
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
Jun 01, 2021 10:05 PM
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!
=======================
Jun 03, 2021 05:37 AM
Hi Jeffrey,
these two articles helped me and might be what you’re looking for:
Jun 03, 2021 06:46 AM
Hi Jonathan,
That works perfectly! Thanks so much!
Jeffrey