Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Number of weeks formula?

Topic Labels: Formulas
1693 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeffrey_Brandt
6 - Interface Innovator
6 - Interface Innovator

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!

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

Traci_Franssen
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Jeffrey,

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

Hi Jonathan,

That works perfectly! Thanks so much!

Jeffrey