Help

Re: Adding Time in a Column?

980 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Alfred_Cooney
5 - Automation Enthusiast
5 - Automation Enthusiast

Would like to see if it’s possible to make a schedule in a Date/Time column by using a “Duration” column and automatically adding it to the the next record…

RECORD A | JAN 16, 2016 8:00AM | +30 minutes
RECORD B | JAN 16, 2016 8:30AM | +15 minutes
RECORD C | JAN 16, 2016 8:45AM | +3 minutes
RECORD D | JAN 16, 2016 8:48AM

Thanks for any help you can provide.

7 Replies 7
Jason_Woltz
6 - Interface Innovator
6 - Interface Innovator

I do maintenance for a living and just dip my toes into this kind of stuff but you had a question whose answer I thought could help me out.

Here is what I have come up with so far.

Appointment Column is a formula running this exactly:

DATETIME_FORMAT(IF(Minutes < 1, CREATED_TIME(), DATEADD(NOW(), Minutes - 1, ‘minute’)), ‘MM-DD-YYYY hh:mm a’)

Minutes is number column where you strictly type the minutes to be added.

I found some issues that would need to be addressed with my code and/or your situation.

A starting point needs to be decided, and it needs to be known if this starting point changes ever. You have an example for one day and a starting time. Is that starting day and time an input?

I was trying to find a solution of not putting a minute input into the minute column, and using the lack of input along with an If statement to then “reset” the day/time from which you could continue adding durations.

I hope this helps!!

Edit: This works better, and I might have a solution here shortly… but this is the latest:

DATETIME_FORMAT(IF(Minutes < 1, CREATED_TIME(), DATEADD(CREATED_TIME(), Minutes - 1, ‘minute’)), ‘MM-DD-YYYY hh:mm a’)

Edit 2:

Tried using MAX(Values) to automatically grab the latest date/time to which add the duration for populating the cell. Max(Values) only seems to work in a niche case with dates. You need to use MAX(Values) in the aggregate section of a RollUp column which references a DATE COLUMN TYPE ONLY, otherwise it does not work. Without this functionality in a formula type column, I assumed you would have to use a LOT of functions to turn the dates and times into test strings, then number strings, then finding the max year, then month, then day, then time, then turning that back into a useable format. Seems like a big nested mess would be required, but I think it is doable.

Jason_Woltz
6 - Interface Innovator
6 - Interface Innovator

More details about use case please. What you are asking exactly in the way you have it formatted is not easily obtainable. I have created some different examples that may give you the same end effect.

Are you recording the minutes after or before the activity takes place?

Jason,
Thanks for the effort on this… I’ve been noodling on it as well to no avail.

The goal is to create a running timeline where the first record has a start time and a duration:

___NAME__| _____START TIME_____|__DURATION MINUTES__
RECORD 1 | JAN 16, 2016 8:00AM | 12

The second record would then auto-calculate its start time that is RECORD 1 START TIME + RECORD 1 DURATION

___NAME__| _____START TIME_____|__DURATION MINUTES__
RECORD 1 | JAN 16, 2016 8:00AM | 12
RECORD 2 | JAN 16, 2016 8:12AM |

Then, when you input a duration for Record 2, it would auto-calculate the start time of Record 3 from RECORD 2 START TIME + RECORD 2 DURATION

___NAME__| _____START TIME_____|__DURATION MINUTES__
RECORD 1 | JAN 16, 2016 8:00AM | 12
RECORD 2 | JAN 16, 2016 8:12AM | 3
RECORD 3 | JAN 16, 2016 8:15AM |

So the tricky part is calculation from record to record, where it seems as though Airtable is only able to calculate WITHIN a single record.

The basic idea is that, as the durations of different records change, all of the start times are updated for all records below the change. It is a running timeline used for planning a sequence of events.

This task alone seems like it would be rather simple in Excel. Unfortunately I have not figured this one yet. and I doubt it will be as simple/ elegant. I have been looking at ways to use a separate column as an initial input for the starting time etc. I will look into this a bit more.

Alfred_Cooney
5 - Automation Enthusiast
5 - Automation Enthusiast

Yup. Super simple in Excel.

But, the power of a relational database is required for all other aspects of my use case so I may just have to look elsewhere for the “timeline” function. C’est la vie.

Did you ever figure out how to do this?
It’s exactly what I need to do, and I haven’t figured out a way yet.

As best I can tell, there are currently two ways to perform such multi-record calculations: Use a SaaS integration service such as Zapier or Integromat to extract a value from one record and write it to another, or use the techniques outline in this post and the base it references. (Note the latter method may require a manual or integrator-driven copy-and-paste to bypass Airtable’s safeguards against circular references.)