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

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

1 Like

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

1 Like

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

4 Likes

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

2 Likes

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

2 Likes

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!

6 Likes

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},[optional 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.

1 Like

@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, [Crops], 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. :wink:

@Sharon_L

You can set up your formula just as you describe it — as long as you have some sort of start date. From your description it sounds as if you have an assumed or implicit start date of today — that is, as of the day you enter the duration. If so,

  1. Define {Duration} as a number field. (There is an Airtable duration type, but it’s intended to measure shorter intervals, such as the length of an audio, video, or film clip, as it evaluates to elapsed time in seconds.)
  2. Define {Due Date} as a formula field with the formula
    DATEADD(TODAY(),{Duration},'days')
    Set formatting for the field to your preferred style (US, European, ISO, etc.).

As for your second question, unfortunately there is no integral critical path/milestone variable type in Airtable; such interlocks must be defined in code. You can find an example of similar functionality in this post and the base referenced in the reply.

While it does not deal specifically with maintaining a critical path, this thread (and the base referenced in replies) demonstrates related functionality — namely, displaying ‘gates’ between product stages.

Finally, this post discusses a method for displaying the ‘next action’ from a prioritized list of actions.

Hey y’all, does anyone know if & how it’s possible to calculate the following Thursday from a given date? For example, if I submit a specific form today or tomorrow, the output would be this coming Thursday, Aug 30 in each case. If I submit on Thursday, Friday, etc. it would then output the following Thursday, Sep 6.

I’ve got this so far:
DATEADD({Client Submission Date}, 11, 'days') which calculates 11 days after the submission date. but, as mentioned I need to specify Thursday.

Hi @Jeremy_Chevallier,

This should do what you want:

DATEADD(
  {Client Submission Date},
  IF(
    WEEKDAY({Client Submission Date})=4, 7,
    IF(
      WEEKDAY({Client Submission Date})=5, 6,
      IF(
        WEEKDAY({Client Submission Date})=6, 5,
        4-WEEKDAY({Client Submission Date})
      )
    )
  ),
  'days'
)

You can copy and paste it as is. Be sure to click Formatting and set “Use the same time zone…” to true or else your dates will be off:

image