I may have something but I needed to add a date, which is kind of logical since if we are the 22/04/18 16h30, when adding 16h, we would be the 23/04/18 8h30.

The {Start time} field is a date field (set on local) which include a time field (in 24h time format without the GMT time zone).

The formula I tried is this one :

DATEADD({Start time},16,'hours')

To which you can apply a formatting so it matches the date and time of the {Start time} field.

It the only thing I could think of, maybe someone with better Airtable skills than mine would do something else :winking_face:

Thanks for the help, very much appreciated

I have changed “Start Time” to Date and then inserted the formula DATEADD({Start time},16,‘hours’)

Here are the results:

For some unknown reason its off by 2 hours. But it looks like it should work.

I got it - my bad

I didn’t check the box “use the same time zone for all collaborators”

With that checked - it works perfectly - you are a miracle work

My bad too :roll_eyes: , I had the same problem when I was testing the formula :grinning_face_with_sweat: and I forgot to mention it in my reply :grinning_face_with_sweat:

But I’m glad to know it finally worked for you

I am not a coder, so when it comes to tweaks like these it becomes a challenge - I am just thankful for good people like yourself who jump into share.

I am all set now and have it operating as needed - many thanks again for the fast & very helpful support.

You’re welcome :winking_face: !

I’m really glad it helped

(I’m no coder either, I’ve got notions of, but I’m learning how to write my own formulas so when I can help, I gladly do it :grinning_face_with_smiling_eyes: )

Hello. I’m glad to have found this thread as I am working on a very similar project and I’m hoping you can offer your insights, and formulas!

I’m new here, so please forgive the non-airtable language.

What I need to create is an itinerary for a bus tour which all happens on the same day and stops at different locations. I would love to create a table which sorta looks like this and makes these calculations:

I’m thinking 2 columns:

Description | time or duration

Departure time | 8AM

Travel time to location A | 20 minutes

Arrival time at location A | adds 20 minutes to 8AM to display 8:20AM

Visiting time at location A | 45 minutes

Departure time | adds the visiting time to the arrival time to display 9:05AM

The tour travels to 5 locations, so I’d repeat that sequence 4 more times. Departure and Arrival times would keep updating as the day progresses.

And we also want a total time traveled at the bottom somewhere which would add all the travel and visiting times up!

So my questions are:

What’s a good way to set this table up?

What type of fields do I use?

What are the formulas I need in the Departure and Arrival times fields?

Once I have one airtable, I plan to duplicate it, so that other tour directors can use my template to plan their day tours!

Thanks in advance and I look forward to hearing from you.

Neil

Hi Scott. I added a post to your original thread with a further inquiry. In the meantime, another contributor suggested I ask you if you’d be willing to share a copy of your airtable as it sounds very close to what I am working on building.

Would you be willing to share?

Mine was very simple and not as complex as what you have outlined, here is the formula I ended with:

DATEADD({Start Time},16,‘hours’)

DATEADD({End Time},-8,‘hours’)

Hope that helps in some small way…

Hi Neil,

Did you ever find a way to do what you are asking in this post? I am trying to do almost exactly what you describe but to build a step by step facilitation guide where the variable is the duration of each step in an activity. It is more complicated than the DATEADD as I’d like to use the duration field (which will vary at each step) to drive the updated time for the next step to start.

Thanks

Ann

Welcome to the community, CSED! :grinning_face_with_big_eyes:

This is actually easier than you might think. The key is remembering that Airtable stores the data for duration fields in terms of seconds, even though it may be formatted as h:mm (by default). When building your formulas to calculate travel or activity times, you will use the “seconds” option in the DATEADD function.

Here’s a quick sample I built:

The only date field is {Depart Meeting Place}, and the GMT switch is active. From there on it’s just duration and formula fields. The formula for {Arrive Location 1} looks like this, with the others being very similar (only changing field names):

DATEADD({Depart Meeting Place}, {Travel Time 1}, "seconds")

Thank you so much Justin! This worked really well.

Hi Ann. Apologies for delayed reply. I gave up on using my beloved airtable. I spent so much time with them which, so far, failed to produce the outcome which I was hoping for.

I’m in the San Francisco Bay area and have spent time with them one on one. I am very impressed with everything they’ve created. I’ll probably revisit their product and services at some point in the future.

In the meantime I got support from the Google forums and a couple of sheets geniuses who helped me better understand the mathematical formulas I needed for my itinerary project.

I don’t know much about the bigger and higher level project which you’re working on. I’m also a little bit confused with some of the language that you are using. I’m sure it makes perfect sense. It’s just me lacking understanding.

If you’re interested in having a closer look at what I have created, let me know. I’d love to share it with you. I’d be happy to continue this conversation and support her as best I can.

Can you say a little bit more about what you’re working on it. And if you’re willing and able, I share your work in progress. Knowing what I know now, I might be able to provide some insight.

I look forward to hearing from you

Cordially,

Neil