Make an employee on call sheet

[Jordan_Friedman]

Hi all! pretty familiar with Airtable however having some issues figuring out best way to accomplish my goal: build an on call sheet for my maintenance team.

ever week of the year a different tech is assigned to be “on call”. For example:

1st week of Jan: Darius
2nd week of Jan: Steve
3rd week of Jan: Al
4th week of Jan: Martin

1st week of Feb: Al
2nd week of Feb: steve
3rd week of Feb: Darius
4th week of Feb: Martin

and so on and so forth for the entire year. Goal would be to have a base that i could easily sort/group by Tech AND month. Additionally, show a calendar view that could show each tech listed for each week.

make sense? can Airtable accomplish this?

thanks!

[Justin_Barrett]

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?