Subtract # of days from a date (RESOLVED)


#1

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 :slight_smile:


#2

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


#3

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.


#4

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?


#5

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.