Skip to main content

Script help: simple formula approach gives me the data I need, but AirTable Calendar breaks

T

I have a table in which I am using a start date+time. The end time needs to be formatted as a date if I want it to work in my Calendar view (which is shared to various departmental users).

I have ‘Class 1 Start’ [2/6/2021 8:00AM], I also have a field where this class has a defined meeting time [105 minutes]. I attempted to use a simple {dateadd} formula to define the ending time of Class 1. Hoping to use the start and end times together in the Calendar View (Airtable - Shared Calendar). Other fields have unique end times so I hard enter them, but we have 5 classes per course so I’m trying to build out a script that would remove the opportunity for data entry errors/oversight. Currently I use it where Classes have no end time, so they populate the calendar as a default 1 hour block. The ical feed thus is incorrect when I share this for use.

I have no experience with Scripts and my cursory search didn’t get me very far. Any guidance where I can piece together a script would be welcomed.

Goal: take start date+time (formatted as a date/time), reference another field to add minutes to start date+time’ to arrive at ‘end date+time’ but the field must be formatted in the table as a ‘date’ rather than a formula.

Failed attempts:
1.Simple formula using dateadd - Formula fields cannot be used in calendar as an end time in calendar view
2.Automation: when Class1 is updated, reference Class1End Formula field and insert in Class1End date formatted field - this seems to be a similar issue, error returned says “Fields” could not be converted to a string.

5 replies

T
  • Todd_Grier
  • Author
  • Participating Frequently
  • 6 replies
  • March 29, 2021

I realize another option may be for a script button where I hand enter Class 1 start and Class 1 End dates+times, then script a copy record for ‘x number of weeks’.

Justin_Barrett
Forum|alt.badge.img+20

Welcome to the community, @Todd_Grier! :grinning_face_with_big_eyes:

Yes they can. I’ve got a couple of calendars (also for classes that I teach) where I do this exact thing, and it works fine. (In fact, both the start and end fields in my case are formulas.)

One thing that you didn’t mention is the field type where you’re storing the class duration. If it’s a number field, then this should work to calculate the end time (changing the field names as needed for your table, of course):

DATEADD({Start Time}, {Duration}, "minutes")

However, if you’re using a Duration field type, it’s important to know that this field will give you the value in seconds, even though you might have entered it in an hh:mm format. In that case, the formula would be the same as above, but changing “minutes” to “seconds”.

If that doesn’t get you on the right track, then we can dig deeper.

T
  • Todd_Grier
  • Author
  • Participating Frequently
  • 6 replies
  • March 30, 2021
Justin_Barrett wrote:

Welcome to the community, @Todd_Grier! :grinning_face_with_big_eyes:

Yes they can. I’ve got a couple of calendars (also for classes that I teach) where I do this exact thing, and it works fine. (In fact, both the start and end fields in my case are formulas.)

One thing that you didn’t mention is the field type where you’re storing the class duration. If it’s a number field, then this should work to calculate the end time (changing the field names as needed for your table, of course):

DATEADD({Start Time}, {Duration}, "minutes")

However, if you’re using a Duration field type, it’s important to know that this field will give you the value in seconds, even though you might have entered it in an hh:mm format. In that case, the formula would be the same as above, but changing “minutes” to “seconds”.

If that doesn’t get you on the right track, then we can dig deeper.


I didn’t try this, but maybe the issue was {Start Time} is set as a type of Date/Time, the {End Time} was set as a formula. In setting up the calendar view, when I used a start time + end time an error popped up.

I went ahead and hand entered these for now, but I’ll tinker with the future events as I plan them.

Thanks for the clarification

Justin_Barrett
Forum|alt.badge.img+20
Todd_Grier wrote:

I didn’t try this, but maybe the issue was {Start Time} is set as a type of Date/Time, the {End Time} was set as a formula. In setting up the calendar view, when I used a start time + end time an error popped up.

I went ahead and hand entered these for now, but I’ll tinker with the future events as I plan them.

Thanks for the clarification


A formula will work for either as long as the formula outputs a valid datetime item. If you run into another issue, share the formula you’re using and we can help.

D
Forum|alt.badge.img+17
Justin_Barrett wrote:

A formula will work for either as long as the formula outputs a valid datetime item. If you run into another issue, share the formula you’re using and we can help.


This still open? Anyway, reading this last post reminded me that twice- and more mirrored formulas don’t really behave too well when it comes to automations.
Like the OP suggested, Airtable can’t read them reliably or at all, depending on the implementation. But this is probably due to the way Airtable’s own infrastructure works and/or offloads work.

While it’s not immediately relevant here, anyone from the future reading this who isn’t aware of DATETIME_PARSE() should check it out if they’re after complex datation mechanisms but don’t want to risk a one-way ticket to migraine express and coding is out of the question.

Reply


Most helpful members this week

Terms & Conditions