Subtract # of days from a date (RESOLVED)

[Calli_Hartman]

I’m aware of the DATEADD function to add a number of days to a particular date, but is there a way to SUBTRACT # days from a date?

Example - I’m building a project plan that backs up from a launch date, instead of a start date. I want to subtract 7 days from my launch date to determine the start date of my week long code freeze, and I want to subtract 21 days from my launch date to determine the start date of UAT testing.

EDIT: Add a minus sign before your #days or reference :slightly_smiling_face:

[Helen_Demoz]

Hi Calli,

I’m new to Airtable and was hoping you could explain how to use the DATEADD function.

I have a number of release dates that I want to add due dates to. I would have a release date in one field for each record (which would be the master date). I’m hoping to use the function you’re using in corresponding fields that would automatically enter due dates by subtracting a number of days from the release date field.

Any help here would be greatly appreciated!!

[W_Vann_Hall]

You described the process perfectly. For Formula field {Derived Date}, 14 days prior to {Master Date}, the formula would be

DATEADD({Master Date},-14,'days')

or

DATEADD({Master Date},-2,'weeks')

(Obviously, adding a positive value would make {Derived Date} later than {Master Date}.)

Here’s a list of unit specifiers (e.g., ‘weeks’, ‘days’, ‘d’). Note that ‘days’ and ‘d’ should work the same way — but earlier today I found that isn’t always the case.

[Helen_Demoz]

Thank you!!! This was very useful…only problem now is that the fields with due dates using DATEADD don’t show up in calendar view :frowning:

Is there a work around?

[W_Vann_Hall]

Lifted from an earlier response:

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.

[Andy_Taylor]

Is there a way to subtract only weekdays? I’m trying to come up with a {due date} 4 work days before the {print date}.

[W_Vann_Hall]

see WORKDAY_DIFF(). :winking_face:

You might also want to see my recent scheduling framework which allows you to define a process template and then instance those processes. The example I use is publishing a newsletter; when you create a ‘publish’ task in your base, it automatically creates and sets dates for ‘write copy’ and ‘proofread content.’ Dates for subtasks (such as ‘write’ and ‘proofread’) are specified as a certain number of days or workdays before or after the main task. So you could define ‘proofread’, for instance, as occurring 4 workdays before ‘publish’, and then when you schedule each month’s newsletter, the date for ‘proofread’ would be set, as well. If you’re trying to schedule a recurring task, I think it should come in handy. (And I’d love to get comments and suggestions on how well it does or does not work!)

[Andy_Taylor]

Thanks. I’ll check it out!

[Jeff_Miller2]

Use the WORKDAY function and add a negative number of workdays.