Jan 13, 2025 09:49 AM
I have a table with records with two fields, 'Start Time', and 'End Time'. Both of these are date fields, and need to remain date fields, i.e. they can't become formula fields.
The issue is that if 'Start Time' is changed, e.g. I change the day, the 'End Time' field does not update. Is there are way to ensure that the 'End Time' field shifts relative to the 'Start Time' field? Importantly, I don't want this to happen in reverse, e.g. the 'Start Time' field should remain where it is when the 'End Time' field is changed.
Hope that all makes sense,
Andy
Jan 13, 2025 11:26 AM
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
Jan 13, 2025 11:38 AM
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:
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).
Jan 14, 2025 12:02 AM
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.
Jan 14, 2025 12:06 AM
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.
Jan 14, 2025 11:16 AM
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:
DATETIME_DIFF({End Date}, {Start Date}, 'seconds')
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.
DATEADD({Start Date}, {Original Duration}, 'seconds')
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!