Assigning due dates to release dates - HELP please

[Helen_Demoz]

I’m hoping someone can help me figure this out.

Is there a way to link a number of due dates to a number of master release dates? For each release date, there will be a number of due dates that tie-in to that particular release date. The due dates for each release date generally never change (though there are a few exceptions that I don’t want to focus on).

As of now, every time a release date moves, I have to manually change the corresponding due dates in different fields.

I would like to assign a number of due dates to a number release dates. When I assign a release date in a field, I would like the corresponding due dates to change so that they reflect the dates that correspond with the release date.

Is there a way to accomplish this without having to manually change due dates in each field?

[Julian_Kirkness]

Hi Helen

I am assuming there must be some logic to the due dates?

In which case you can use formula fields with date calculations - probably using the DateAdd() and Workday() functions. You can find out more here:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

Hope this helps!

[Helen_Demoz]

Hi Julian,

Thank you for responding to my question!!!

Yes, there is logic to the due dates. I’m still a bit confused as to how I would write the formula though.

I have a release date in field 1. Example: 3/23/2018
First due date is two month’s before release date (field 2). Example: 1/28/2018

If the release date in field one changes to 3/30/2018. First due date would then be 2/2/2018.

How would I write the formula so that each time the date in field one changes (release date), the due dates would automatically change to two months in advance and still have them appear in calendar view?

Hope that makes sense. For background, the release dates are for album release dates (that constantly change…annoying but true) and the first due date is the deadline for product approval (there’s 7 more due dates for each release date too). Trying to figure out a system to streamline the process of keeping everyone on the same page and aware of deadlines/due dates.

Thank you!!!

[W_Vann_Hall]

Well, if you have a Date field {Release Date}, then Formula field {First Due Date} would be configured as

DATEADD({Release Date},-2,'m')

That subtracts 2 months from — well, adds -2 months to — {Release Date}. For your other derived dates, add or subtract the appropriate period of time; you can find a list of unit specifiers here.

Note: While putting this together, I found what appears to be a problem with how DATEADD() handles long vs short specifier forms.

[Helen_Demoz]

Thank you!!!

Is there a way to make the due dates (with formulas) show up in calendar view?

[W_Vann_Hall]

As long as Airtable understands your formula to be a date — that is, when you select ‘formatting’ under ‘Customize field type,’ is presents you with formatting choices applicable to dates — you should be able to display them in calendar view. (Since they are calculated dates, you won’t be able to change the date by dragging records from one day to another, and you won’t be able to create new records by clicking on a day.)

If Airtable doesn’t see the field as a date, it’s probably because something in your formula is causing it to be cast as a string. If that is the case, you can wrap it with DATETIME_PARSE() to change it into a date. See the section on date and time functions for the exact syntax to use.

[Caspian_James1]

This has been really useful, thank you.

The only problem is that I’m not given a lot of #ERROR! notifications if there isn’t an initial date (half of my inputs don’t yet have initial dates). How could I parse an IFERROR in to this to return a blank?

[W_Vann_Hall]

I’m a little unsure what you are asking.

If the problem is that you want to have the formula return a blank field rather than #ERROR! if the initial date is empty, the easiest way is to preface your formula with a test of {InitialDate}:

IF(
    {InitialDate},
    [Formula Stuff Goes Here]
    )

If the problem is you wish to return an error condition when {InitialDate} is empty, try

IF(
    NOT(
        {InitialDate}
        ),
    ERROR(),
    [Formula Stuff Goes Here]
    )

If the problem is something else entirely, run it by me again and I’ll give it another whack.

[Caspian_James1]

That first equation fixed it. Thanks so much!

[James_Nonu-Carl]

Hi Julian and W,

I understand the above, and have created the below formula to ensure my delivery deadline is 1 month before my release date.

My question is: how would I change the below formula to ensure the delivery deadline lands on the Thursday of the week it falls into?

DATEADD({Release Date},-4,‘weeks’)