Creating a Timeline with Computed Calendar Data

[SC_Maree]

I work for a photography company so all of my order data is imported from the photographers calendar, it then needs to be edited by our production team. I bring in the data manually from our calendar program as a .csv each day. I am trying to create a calendar for each of our retouchers so they are not overloaded.

I have created a new table with each of our products and an estimate for editing time. The issue is the calendar doesn’t like computed dates. As I am using the photographers calendar the date is from yesterday and also the end time is for the photo shoot, therefore will be longer than editing time. I currently have the “date created” field (batch import same time each day), then a field for “local time zone”, a lookup field for “editing time” and a DATEADD calculation to get the “end time”.

My question is, how do I then show these on a calendar or timeline so I can visually see when someones day is full. Ideally they would be stacked in the order they are listed under each retoucher in my grid view.

If not able to do it visually, is it possible to have it calculate a completion time, based on how many orders are in the queue for each retoucher above it?

Thanks so much for any insights :sunny:

EDIT: I have manually made a date field and can paste the date over to my other fields very quickly each day, this means I can move things around in the calendar view very easily. The issue is just not being able to adjust the appointment duration time.

EDIT: I have tried to do the calculations in ‘numbers’ on a mac but when I copy and paste into airtable, it leaves the computed date cells blank.

[Justin_Barrett]

SC_Maree:

The issue is the calendar doesn’t like computed dates.

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?

SC_Maree:

EDIT: I have manually made a date field and can paste the date over to my other fields very quickly each day, this means I can move things around in the calendar view very easily. The issue is just not being able to adjust the appointment duration time.

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.

[SC_Maree]

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.

[Justin_Barrett]

SC_Maree:

I’m unable to drag and drop the records to rearrange the order and change the start/end times that way.

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.

[SC_Maree]

Thanks Justin, this is the type of workaround I was hoping for :slightly_smiling_face: Thank you very much!

[Brenda_Ginsberg]

Justin_Barrett:

dates are calculated based on a single start date for the term

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

[Justin_Barrett]

Brenda_Ginsberg:

What formula do you use to achieve this?

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.

[Brenda_Ginsberg]

@Justin_Barrett, Thanks so much for your thorough insight and time! I appreciate it. I will try to implement this and come back if any major questions.