Relative Due Dates

Daniella_Delato · ‎May 07, 2018

Hey Airtable, I need your help!!!
I am a Project Manager and we plan for our projects based on the date we start construction.
All tasks that need to be completed are categorized by when we need to complete them, for example, the oven needs to be ordered 3 weeks before construction starts, so “Order Oven” would be under “week -3”.
That “week -3” would have a date assigned to it, relative to the “start date” so the formula would look something like ({startdate}, -3, ‘weeks’). That way, if the “start date” changes, then the rest of the schedule would automatically shift.
I’ve tried to play around but i can’t find a way to do this… please help!!!

Jeremy_Oglesby · ‎May 07, 2018

You are on the right track - I think you may just be forgetting to declare the function you are using:

DATEADD(startdate, -3, 'week')

That formula in a formula field will do what you are wanting, assuming your “Start Construction Date” field is actually called startdate. If not, replace startdate with the name of your field.

Daniella_Delato · ‎May 07, 2018

The function works, what doesn’t work for me is that now everything on that column will have the same formula… I can’t change the formula for each specific cell, so all my due dates will be the same within that column

Jeremy_Oglesby · ‎May 07, 2018

I’m having a little trouble visualizing what your table might look like.

If possible, I might be able to help you better if you can duplicate your base (dropdown menu on base icon in your home screen) and uncheck “Duplicate Records” so that your info is not visible in the duplicate. Then create a share link for your duplicated base and paste that share link here.

That will allow me to see your base structure without seeing your records and then I can more easily see what you are wanting to do.

Daniella_Delato · ‎May 07, 2018

I haven’t really built anything out so I don’t have a lot to show you… But the photo above is a picture of my excel spreadsheet. Each date you see is linked to the highlighted “start date” and every time i change that one date, the rest of them automatically shift.

Chris_Parker · ‎May 07, 2018

You need to put your “-3” in its own column, then hide it, and then reference it in your formula. E.g. The field could be called Days Prior to Start Date.

DATEADD(startdate, {Days Prior to Start Date}, 'days')

To add on to this, you would want to use a Formula to set the value of Days Prior to Start Date. Have it reference Timeline to determine its value.

Jeremy_Oglesby · ‎May 07, 2018

Ah, ok - so you might have to rethink the way you structure this in Airtable. Airtable is more database than it is spreadsheet.

What @Chris_Parker just suggested may work to do what you need.

But you also might consider structuring your timeline the other direction - where “4 weeks before construction” is a field (column), rather than a record (row). This would probably require the restructuring of other data as well, but I think it might benefit you in the long run to think of things this way.

Chris_Parker · ‎May 07, 2018

Can you expand on this? Are you suggesting to have a column for each Timeline period so that all possible relative dates can be viewed at a glance?

Jeremy_Oglesby · ‎May 07, 2018

Yes, I think so. It’s hard to know if that would be beneficial or not without seeing the rest of her data structure.

I’m just making a general point that I have found cases where I needed to take what I used to use as “rows” in Excel and make them “fields” in Airtable in order to accomplish what I needed to do. Usually it had to do with a formula acting on a date or some other number related to a record (like a project) - since a formula can only act on fields in a single row, I’ve often had to find a way to restructure my data so that everything I need to act on with a formula is in a single “row”, or “record”.

Does that clarify?

Daniella_Delato · ‎May 07, 2018

If I do it this way, then everything under each column would have to be a formula right?