There is a date dependency option but that's on paid plans. is the issue with not having them formula fields because of formatting? if so there is an option to format formula fields as a date so it shouldn't break anything else (e.g. keep Start Date as is, add a "Days to End" number field, and change End Date to a formula formatted as a Date with the formula DATEADD({Start Date},{Days to End},'days').
If that doesn't work, the other option is to use Airtable automations, e.g. add a 'Last Modified Time' column that only looks at Start Date, and have that trigger the automation to update End Time
You can do this with automations, but the implementation varies based on what rules the change in date usually follow.
Here are a few different examples that would require different implentations:
- The End Time is the same actual time, but it needs to be on the same day as the Start Time
- The End Time needs to be a certain fixed value of time from the Start Time (ie. 1 hour, 1 day, 1 week, etc)
- The amount of time between the Start and End Time varies based on external conditions (ie. a fixed project length, a 30 min vs 60 min meeting, etc)
If you can provide a bit more context to clarify the above, I can provide a more specific solution.
The general idea is to create at least one additional field that reports the expected End Time relative to the Start Time, then have an automation that runs and updates the End Time if it does not match the expected End Time.
In some cases, you can even forgo the automation and just have the End Time field itself be a formula field that calculates an End Time relative to the Start Time (formula fields with the correct output can still output as date fields, they just don't accept manual input).
There is a date dependency option but that's on paid plans. is the issue with not having them formula fields because of formatting? if so there is an option to format formula fields as a date so it shouldn't break anything else (e.g. keep Start Date as is, add a "Days to End" number field, and change End Date to a formula formatted as a Date with the formula DATEADD({Start Date},{Days to End},'days').
If that doesn't work, the other option is to use Airtable automations, e.g. add a 'Last Modified Time' column that only looks at Start Date, and have that trigger the automation to update End Time
I am on a paid plan (Teams). I did not know about Date Dependency. I have just tried to set it up, but it says that the start field 'cannot include time'. Eh? I am managing a calendar, which has date and time. Any way round this? I have to say it seems basic functionality and if Airtable can't automatically update end times based on a start time change, I am quite disappointed in it.
You can do this with automations, but the implementation varies based on what rules the change in date usually follow.
Here are a few different examples that would require different implentations:
- The End Time is the same actual time, but it needs to be on the same day as the Start Time
- The End Time needs to be a certain fixed value of time from the Start Time (ie. 1 hour, 1 day, 1 week, etc)
- The amount of time between the Start and End Time varies based on external conditions (ie. a fixed project length, a 30 min vs 60 min meeting, etc)
If you can provide a bit more context to clarify the above, I can provide a more specific solution.
The general idea is to create at least one additional field that reports the expected End Time relative to the Start Time, then have an automation that runs and updates the End Time if it does not match the expected End Time.
In some cases, you can even forgo the automation and just have the End Time field itself be a formula field that calculates an End Time relative to the Start Time (formula fields with the correct output can still output as date fields, they just don't accept manual input).
Thanks for your reply. I am trying to use Airtable as a replacement for our events calendar in our organisation.
In regular calendars, if someone changes the the start time, the end time automatically moves as well.
In Airtable, when someone changes the start time, the end time stays fixed, which means that many of our events have a start time either a week in the past, or a week in the future, if e.g. the start time is moved by a week.
I know I could set a 'duration' field, and have the End Time be automatically calculated, however this is very unintuitive for our employees who are used to using digital calendars. I don't want to add unnecessary complications.
Honestly, I thought Airtable would be able to mimic this basic calendar functionality seeing as this is one of the things it claims to be able to replace.
Thanks for your reply. I am trying to use Airtable as a replacement for our events calendar in our organisation.
In regular calendars, if someone changes the the start time, the end time automatically moves as well.
In Airtable, when someone changes the start time, the end time stays fixed, which means that many of our events have a start time either a week in the past, or a week in the future, if e.g. the start time is moved by a week.
I know I could set a 'duration' field, and have the End Time be automatically calculated, however this is very unintuitive for our employees who are used to using digital calendars. I don't want to add unnecessary complications.
Honestly, I thought Airtable would be able to mimic this basic calendar functionality seeing as this is one of the things it claims to be able to replace.
Airtable is more of a sandbox than a native calendar app. It has some calendar functions but it is not built to be an out-of-the-box, feature-rich calendar app. That said, it does give you the tools to make it function however you'd like while they (hopefully) catch up on adding more user-friendly functions (like allowing/accounting for time when setting up date dependencies).
In a calendar view, as long as you have the "End Date" field set in the view settings, a user physically dragging a calendar event around will automatically update the start and end date to preserve the original duration.If you're looking to have direct edits to the start time change the end time, you can do the following:
- Create a formula field that calculates the duration of the event using let's call it "Event
DATETIME_DIFF({End Date}, {Start Date}, 'seconds')
- Create a duration field that we'll call "Original Duration"
- Create an automation that copies the original "Event Duration" to "Original Duration"
Trigger: When a record is created, or When a record matches conditions**
Action: Update Record
Original Duration => {Event Duration}
**If records are created with all data populated immediately (like through a form submission), you can use "When a record is created." If not, you'll want to use the latter trigger, tailored to your record creation process.
- Create another formula field that calculates an End Date based on the Start Time and the Original Duration, we'll call it "Calculated End Date"
DATEADD({Start Date}, {Original Duration}, 'seconds')
- Finally, create an automation an automation to process the update you're looking for
Trigger: When a record is updated
Fields: Start Date
Action: Update Record
End Date => {Calculated End Date}
This setup will not require any of your employees to manually input durations.
Hope this helps!
