Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

How to have 'End Time' field automatically time shift when 'Start Time' field is updated

Topic Labels: Dates & Timezones
495 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Andy
5 - Automation Enthusiast
5 - Automation Enthusiast

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

5 Replies 5
ibayub
6 - Interface Innovator
6 - Interface Innovator

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 

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

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).

Andy
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Andy
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

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!