- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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:
- 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).
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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:
- 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!
