Help

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.

Number of weeks formula?

Topic Labels: Formulas
2163 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