Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Number of weeks formula?

Topic Labels: Formulas
452 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

3 Replies 3

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

Screenshot 2021-06-02 at 06.01.18

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