How do I add a set number of days to a date?

[Kyle_Groger]

Hi,

I’m using Airtable to manage my library. I was hoping I could use it to automatically calculate the due date based on the date I loaned the book out. For example, if I loaned the book on the 5th of October, I want to add 14 days and have a column saying 19 October for due date.

How do I do this?

Regards

[Julian_Kirkness]

Hi Kyle

You can create a formula field as follows:

DATEADD({Date 1}, 10, ‘days’)

(Where {Date 1} is the field to which you are adding the 10 days.

Note that this always results in a date formatted in US format (with the month first). If you want to format the date to suit your local taste the you can use something like:

DATETIME_FORMAT(DATEADD({Date 1}, 10, ‘days’), “DD-MM-YYYY”)

More information is in the Formula Field reference section in Help.

Another important note is that the result is a Text value rather than a date and so can’t be used to display things on a calendar View. [Airtable - it would be great to be able to specify a type for a formula field!].

Julian

[Kyle_Groger]

Thank you! It worked for me

[Ray_Williams]

Kyle/Julian
I’m looking for a WORKDATE or similar function in airbase. I need to program in a TARGET DUE DATE for each client upon receipt of application to set their expectation. I did this using DATEADD function - worked great, but …

TARGET DUE DATE results are falling on Weekends and Holidays.
:frowning:

Any suggestions for functions or workarounds?

@Airtable_Support: Adding WORKDATE or similar function(s) will have huge appeal to anyone in project management (which lately seems to be everyone - one way or another)

[Julian_Kirkness]

Hi @Ray_Williams

This is still possible without a Workdate function - it’s a matter of using If and Weekday() to determine which day of the week we are on and then adding the appropriate number of days. The example below adds two workdays to an entered date, for example:

IF(WEEKDAY({Date 1}) > 3, DATEADD({Date 1}, 4, ‘days’),IF(WEEKDAY({Date 1}) = 0, DATEADD({Date 1}, 3, ‘days’),DATEADD({Date 1}, 2, ‘days’)))

Note - WEEKDAY returns a 0 for Sunday up to a 6 on Saturday. So, this formula adds 4 days for a start date on Thursday, Friday or Saturday, 3 Days on a Sunday and 2 days on Monday, Tuesday or Wednesday.

Note this returns an American date format an if you want something else you can apply the formatting described above.

Hope this helps.

Julian

[Ray_Williams]

Thanks Julian that’ll work!

[Phil_Lauer]

@Julian_Kirkness

I’m thinking the formula you offered works if the number of work days being added crosses only 1 weekend. For each additional weekend crossed, the calculated date is 2 days less than what it should be. Any ideas?

Thanks
Phil

[Julian_Kirkness]

Hi @Phil_Lauer - you are quite correct, the formula for any number of days added would be slightly different and require a modified formula - but structurally the formulae would be similar and would just need to account for the different additions for different starting days (Date 1). There are some simple cases as well - for example to add 5 working days you would always add 7 days - as with any multiple of 5 actually.

[Caitlin_Davies]

Hi guys!

I have a datefield that I want to add to a number, which is another field that’s automatically populated from another table. For instance:

Date of Planting: 6/1/2017
Days to Maturity: 75

I want to add Planting + Maturity, but DATEADD doesn’t seem to allow an input other than an integer in the COUNT section. I.e., I want to do this;

DATEADD({Date of Planting},{Days to Maturity},‘days’)

With an output of ‘08/15/2017’

But it’s not working!!! I can do this function very easily in Excel, please help!!!

Thank you! :slightly_smiling_face:

[Matt_Bush]

From your description, it sounds like the {Days to Maturity} field is a Lookup of a number field.

Because Lookups can contain multiple linked values, in formulas they’re represented as an array. Sometimes Airtable’s formula engine will automatically recognize and convert these arrays, but in other cases like DATEADD you have to manually convert from an array to a single value. ARRAYJOIN will convert from an array to text, and VALUE will convert from text to number.

Try this modification:
DATEADD({Date of Planting},VALUE(ARRAYJOIN({Days to Maturity})),'days')