Number of weeks formula?

[Jeffrey_Brandt]

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

[JonathanBowen]

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!

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

[Traci_Franssen]

Hi Jeffrey,

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

Airtable Support

Grouping on a date range

If you want to group your records on a date range—for example, to see every transaction record that was made by week, or every deal by the quarter in which it occurred—you can create a formula fiel...

For The Record – 6 Nov 18

The top 10 time-saving Airtable date formulas

Use these powerful formulas to stay on top of due dates and deadlines.

[Jeffrey_Brandt]

Hi Jonathan,

That works perfectly! Thanks so much!

Jeffrey