Creating a Timeline with Computed Calendar Data

My guess is that there’s an issue with your DATEADD formula. I’ve got a calendar for one of my side jobs that is almost nothing but calculated dates—it’s an 11-week course that I teach, and all class dates are calculated based on a single start date for the term—and they all appear without any issues. Could you please share the formula you’re using, and any relevant details about the data being used in the formula, including the data types of referenced fields?

If your base is in a free Airtable workspace, durations can’t be set in a calendar view. That’s a Pro plan feature. However, if you’re in a Pro plan workspace and are still having duration issues, I’m guessing it’s in the formula I asked about above.

Thanks for your response, I really appreciate it.

To be clear, the calendar does show the calculated end time, but I’m unable to drag and drop the records to rearrange the order and change the start/end times that way. I was hoping to find a way to import each record with the same start time, and calculated end time, and manually arrange them in the calendar to figure out an order as I understand airtable is unable to calculate based on the end time of the record above it.

I have a pro plan at the moment.

As a compromise I’m thinking I’ll just calculate the total number of hours in duration for each editor and highlight it when over a max hours for each person depending on their schedule. Calendar would have been ideal as I can give client a predicted time of completion.

That’s because the end time is driven by a formula. Formula fields don’t create data that can be manually edited. The end result is always driven by the formula. The only way to change the output is to change the data driving the formula’s calculation(s). If you want a calendar entry that you can drag around, you’ll either need to copy the calculated end time into a regular date field, or use a third-party tool—either custom API code, or something like Zapier or Integromat—to do the end-time calculation and update a regular date field.

My guess is that there’s an issue with your DATEADD formula. I’ve got a calendar for one of my side jobs that is almost nothing but calculated dates—it’s an 11-week course that I teach, and all class dates are calculated based on a single start date for the term—and they all appear without any issues. Could you please share the formula you’re using, and any relevant details about the data being used in the formula, including the data types of referenced fields?

If your base is in a free Airtable workspace, durations can’t be set in a calendar view. That’s a Pro plan feature. However, if you’re in a Pro plan workspace and are still having duration issues, I’m guessing it’s in the formula I asked about above.

Hi there, What formula do you use to achieve this? Thanks so much in advance.

I actually modified the setup since I wrote that, so it’s no longer one date driving everything. The new setup isn’t too different, and it’s actually a lot easier to maintain than my first attempt.

The class I teach meets twice per week—once for that week’s lesson, another for review—and I have both morning and evening classes. To set this up, I made a table named [AM/PM] that contains only two records:

I also have a [Sessions] table where I laid out the schedule for a single class for the entire term. In the {A/B} field, A represents the first session in the week (the review session), and B is the second (lesson) session of the week. (There are more fields, but I’m only showing what’s relevant to the calendar setup)

I then made a [Schedule] table, where I built both morning and evening classes by linking to appropriate records from [AM/PM] and [Sessions]. This part only shows the beginning of the morning class setup. A similar set of records is farther down the table for the evening class.

I use rollup fields to pull in the relevant datetime items from the [AM/PM] table, so that each of the records has the correct start time for the very first class of the term.

Because I’m only pulling in a single value, using values for the aggregation formula returns a single datetime item, not an array. I left them unformatted, as I need them to stay as datetime items for later calculations.

Now comes the formula fun. In my case, I also have systems in place to allow for a session to be canceled or rescheduled, and that’s all handled by this one formula as well. The core of it, however, just adds the appropriate number of weeks to the appropriate base datetime item based on the session’s specified week and A/B (review/lesson) status.

IF(
    OR(
        Override,
        Cancel
    ),
    IF(
        Override,
        Override
    ),
    DATEADD(
        SWITCH(
            {A/B},
            "A", {Review Session Base},
            "B", {Lesson Session Base}
        ),
        Week - 1,
        "weeks"
    )
)

With these dates calculated, I built separate calendar views for both morning and evening classes, along with iCal links which I use myself and share with my students.