Here’s something I came up with. The only thing that’s tough about your requirements is the calendar view. Airtable’s Calendar view places items on the calendar based on a specific date. There isn’t currently an option to have an entry span an entire week unless you provide a start date on Sunday and an end date on Saturday. That would require the base to be in a Pro workspace to support the end date feature. If it’s good enough to show the tech only on the first day of the week, then this solution will do the trick.
Here’s how the table looks after setup is complete:
At the heart of the week-by-week date calculation is an autonumber field named {#} , which drives the formula in {Week Beginning}:
DATEADD(DATETIME_PARSE("01/05/2020"), {#} - 1, "weeks")
I picked the first Sunday in January as the start date, but feel free to tweak that as you see fit.
The {Month} field extracts the month name from that date:
DATETIME_FORMAT({Week Beginning}, "MMMM")
The {Tech} field is a single-select, but could just as easily be a link to another table if you prefer. I wouldn’t recommend using a single line text field unless you’re a stickler about consistent spelling and capitalization. :slightly_smiling_face:
As you can see from the above screenshot, there are four hidden fields doing some extra work. One is the autonumber field mentioned previously. Another calculates the end of the week by adding 6 days to the start date. Yet another pulls the month name from that end-of-week date. Finally, to make grouping by month easier, I made a formula field named {Sorted Month}:
MONTH({Week Beginning}) & " - " & Month
That outputs names like “1 - January”, “2 - February”, etc.
The primary field is a formula that creates an at-a-glance look at a given week and the tech who’s assigned to work it:
LEFT(Month, 3) & " "
& DATETIME_FORMAT({Week Beginning}, "D") & "-"
& IF(Month != {Month End}, LEFT({Month End}, 3) & " ")
& DATETIME_FORMAT({Week Ending}, "D") & ": " & Tech
In a “Month” view, I grouped by that {Sorted Month} field to make this:
In a “Tech” view, I grouped by tech:
Adding more weeks is just a matter of adding more records. The autonumber field continues counting automatically, so the dates keep going up week by week without any input from you.
Is that close to what you’re looking for?
