Week Day Calendar Automation

@Adrian_Raszkiewicz There are a couple AT Support Articles that may help:

• If you need recurring dates done manually, you can use the fill handle rather easily to get it to auto fill. (See Recurring Dates Article)
• If you need automatically recurring records, you can set up an automation based on the triggers When record matches conditions or When record enters view or trigger it once a week using the At a scheduled time. (See Recurring Records Article and Date Addendum at the Bottom)

While both are viable options, they both have limitations. The first is clearly limited in that it is a manual process. The second is limited in that it requires more overhead. If you do not have the pro plan or are already reaching your automation limit, this option loses traction quickly. Furthermore, more overhead means a system is often more fragile. Small changes may break your automations.

However, there is another alternative using a formula. Rather than doing an automation that sets it up, you can use a formula that parses the date from a Single Line Text field and autogenerates the date based on this info and the ‘Day of the Week’ information. By doing it this way, the calendar will dynamically move from week to week as you hit Sunday without any manual entry.

While I will give more details below, I figured the easiest way to get started for those who just want it to work or are more visual learners was to see a working solution:

Formula Information

The formula used to calculate the Start/End Date is as follows:

DATETIME_PARSE(
  DATETIME_FORMAT(
    DATEADD(
      TODAY(),
      SWITCH({Day},
        "Sunday", 0,
        "Monday", 1,
        "Tuesday", 2,
        "Wednesday", 3,
        "Thursday", 4,
        "Friday", 5,
        "Saturday", 6
      ) - WEEKDAY(TODAY()),
      "days"
    ),
    "MM/DD/YYYY"
  ) & " " & {Start Time},
  "MM/DD/YYYY h:mma"
)

To give a brief explanation, the DATETIME_PARSE function takes two parameters: A string with the date, and the format to expect. As you can see, to create the string version of the date, I actually use the DATETIME_FORMAT function to return a string version of the proper day of the week.

By using the selected {Day} field, I can return the proper date of the current week using a combination of the DATEADD function, the TODAY function, the SWITCH funciton, and the WEEKDAY function. This combination will return a string of that day’s date (i.e. "05/10/2021"). I simply append the {Start Time} field on the end, making sure it’s formatted as I requested. Since I requested a time in the format h:mma, {Start Time} should be inputted like so: 8:01am or 11:13pm. Thus, I have a string that may look something like this: "05/10/21 8:01am".

By passing the string version of the date to the DATETIME_PARSE function, I am able to format my date appropriately and use it in the calendar, as can be seen in the base attached.

Implementation Flexibility

What’s cool about this implementation is that one can have as many Monday or Tuedsay blocks as they want. If they take multiple breaks throughout the day, this implementation can support that very easily. As you can see in the example base, a lunch break was easily factored in by simple adding two records for each week day.

If you wanted to do more than just weekdays, this can also work. By default, the week starts on Sunday based on the formula implementation. Thus, you could easily select Sunday and Saturday times as well if necessary.

You can also add additional people. By having a person’s table and linking them, it becomes trivial to add multiple employees. Three employees were used in the example base. By using a formula as the primary field, one can pull the name of the employee so that their name can appear as the title of each calendar block. While the example creates separate views for each employee, this could also be done by grouping based on the linked field instead to limit the number of views.

Implementation Limitations

The current set up only allows you to do it based on the current week. If you look a week ahead, it will be empty. While it will carry over to next week once you enter next week, you won’t be able to see it on the calendar until you get there.

Extra Bells and Whistles

I added a Color selector with all of the colors that AT offers. The beauty of this is it allows you to easily color code each calendar block as desired. Not really necessary, but it makes the calendar more interesting. The example base sets all the records for a given individual to a certain color to make it easy to differentiate between employees when you are looking at the combined calendar.