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. aAirtable - it would be great to be able to specify a type for a formula field!].

Julian

Thank you! It worked for me

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.

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)

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

Thanks Julian that’ll work!

@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

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.

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!

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

Matt Bush, you are my hero! Thanks for the help, that worked perfectly!!

Any updates on if they’ve added a workday function? I’m hoping to use for project management in calculating deadlines automatically.

Plus 1 for this feature. Real Estate contracts- I need to add a certain number of days to a date, and they cannot END on a weekend or holiday. There can be weekends and holidays in the middle, but it cannot end on one. Would be huge for me, and the millions of people (lender/title/escrow/realtors) in the industry.

Conversely, I also need to be able to subtract a certain number of days from the end date. Should be easy once the above is implemented.

Happy to share that we now have a WORKDAY() function!

The format is WORKDAY(startDate, numDays, [holidays]) and it returns a date that is numDays working days after startDate. Working days automatically exclude weekends, and you can include an an optional list of holidays as a comma-separated string of ISO-formatted dates, e.g.

WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10')

@Valerie_Miles


For real estate contracts, you should now be able to use a formula similar to this:

WORKDAY(DATEADD({Input Date},{Days to Add or Subtract}-1,'days'),1,'2017-09-04')

Let me know if this works!

I’m new to AirTable and trying to create a formula for start date. We have a date field for due date and a number field for lead time. I’d like to create a formula that takes the due date subtracts the number of lead time days to give us a start date. Any help would be appreciated.

Pretty much just as you describe it:

DATEADD({Due Date},-{Lead Time},'day')

If you need business day, try

WORKDAY({Due Date},-{Lead},aoptional list of holidays])

hi julian!


this is exactly my problem: i have a start date and the due date is calculated with dateadd. but in calendar view it’s impossible to set the ‘end date field’ based on those fields. do you know of a workaround? thanks!

I’ve just successfully set up a calendar view with an entered field and a formula field and it worked fine - you do have to format the formula field though.

@Julian_Kirkness and @Matt_Bush


I am trying to set up a project and want to use duration (instead of “date”) as a field to set up the schedule. Right now I am manually entering “due date” and then have a formulation for duration days as reference, but I’d like to have it vice versa- where I enter the duration days and the due date will be populated. Is there any way I can do that?

Also, Is there any field where I can link each different tasks as predecessors- such as certain task B cannot start until task A is complete. Any feedback or input you can provide is highly appreciated!

I’m looking for the same thing. My company, a wholesale greenhouse uses “crop times” to calculate ship dates in our production system. I want to set up a similar system for my home garden. A planting date + a crop time (a number of days) = a projected harvest date. This would be pretty handy. In our database at work the dates get converted to week numbers so it is easier to calculate. I might just do a similar solution so I can just do it with integers.

Your scenario is easy to handle under Airtable.

1. Define {Planting Date} as a date field.



2. Define {Crop Time} as a number field.



3. Define {Harvest Date} as a formula field with the formula
   
   
   DATEADD({Planting Date},{Crop Time},'days')
   
   
   Set formatting for the field for your desired date format (presumably without a time field.

If you want to enter {Crop Time} in weeks, simply change the unit specifier in the DATEADD() function from 'days' to 'weeks'.

Alternatively, you could create another table, , that would include (among others) such fields as {Crop Name} and {Crop Time}. You could then use a lookup field to populate {Crop Time} in the DATEADD() function automatically, based on what you planted (possibly with a multiplier based on how early or late in the year {Planting Date} is. :winking_face: