Assigning due dates to release dates - HELP please

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:

Hope this helps!

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!!!

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.

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

Thank you!!!

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

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.

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

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?

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.

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

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

That first equation fixed it. Thanks so much!

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’)

This is how you’d get the Thursday of the same week for any given date:
DATEADD(Date,4-WEEKDAY(Date),'days')

So, putting this formula together with yours, you’d get:
DATEADD(DATEADD({Release Date},-4,'weeks'),4-WEEKDAY({Release Date}),'days')

Note that you don’t need to sub in your formula into the WEEKDAY function, because DATEADD with ‘weeks’ always returns the same day of the week.

Formula modified from:
https://exceljet.net/formula/get-first-monday-before-any-date

Note: Airtable’s WEEKDAY function is not compatible with Excel.

Fantastic, thank you so much Andy - that worked a charm.