Adding Time in a Column?


#1

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.


#2

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.


#3

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?


#4

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.


#5

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.


#6

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.