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

W_Vann_Hall · April 7, 2018, 2:49am

Your scenario is easy to handle under Airtable.

Define {Planting Date} as a date field.
Define {Crop Time} as a number field.
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.

W_Vann_Hall · April 7, 2018, 3:18am

@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,

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

Jeremy_Chevallier · August 28, 2018, 10:07pm

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.

Jeremy_Oglesby · August 28, 2018, 10:50pm

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:

Jeremy_Chevallier · August 30, 2018, 8:44pm

Hey Jeremy, thank you so much! This works perfectly, however I’m not sure I understand how it works. For example, if we beed to instead calculate 2 Thursdays from {Client Submission Date}, I would expect that simply changing 7 to 14, 6 to 13, 5 to 12 should do it? But this change doesn’t seem to have any effect on the output.

Thanks again.

Jeremy_Oglesby · September 3, 2018, 4:19pm

Hey @Jeremy_Chevallier - been camping the last few days, sorry.

The WEEKDAY() function returns the number of the day of the week for the date you pass it. If you pass it a date that falls on a Sunday, it returns 0, if you pass it a date that falls on a Saturday, it returns 6 - I’m sure you can fill in the blanks.

So to get the 2nd Thursday from a date, you have to add a number of days that gets you to the 4th day of the week (day that returns 4 in the WEEKDAY() function), and is also at least 8 days away.

In the formula I sent you above, I am saying:

When the day# = 4 (ie, it is a Thursday), add 7 days to get my new date
When the day# is 5 (Friday), add 6 days…
When the day# is 6 (Saturday), add 5 days…
When the day# is anything else (ie, anything less than 4), just subtract the day number from 4 to get the number of days until Thursday and add that many days… (Sunday = day#0; 4-0 = 4; adding 4 days to the 0th day gets you the 4th day, which is Thursday… and so on for Monday, Tuesday, and Wednesday).

Hopefully that helps you understand what’s going on there. I’m going to leave it at that for you, and let you try to figure out how to make your new formula for finding the 2nd Thursday - but if you still can’t figure it out and need help, feel free to post back.
(Also, you are totally on the right track with the changes you proposed — there is just one more change you need to make in addition to them to make any days Sunday-Wednesday work).

Jeremy_Chevallier · September 4, 2018, 12:31am

Thanks man!! I got it to find the next Monday that is at least 5 days away:

DATEADD({Writer's Due Date}, 
  IF(WEEKDAY({Writer's Due Date})=1, 14, 
    IF(WEEKDAY({Writer's Due Date})=2, 13, 
      IF(WEEKDAY({Writer's Due Date})=3, 12, 
        IF(WEEKDAY({Writer's Due Date})=4, 11, 
          IF(WEEKDAY({Writer's Due Date})=5, 10, 
            IF(WEEKDAY({Writer's Due Date})=6, 9, 
              IF(WEEKDAY({Writer's Due Date})=7, 8, 
      8-WEEKDAY({Writer's Due Date})
      ))))))),'days')

Kattie_Rogers · November 20, 2018, 8:29pm

THIS!! So glad I found this comment - I couldn’t understand why the formula wasn’t calculating properly!

Nataliya_Bondarenko · June 4, 2019, 10:00am

It’s not working for me… I am so disappointed! HELP!!!
I simply need +6 workdays from Date of Request…

Nathalie_Collins · June 4, 2019, 10:22am

@Nataliya_Bondarenko - Adding a set number of days to an existing date is a little different.

Try DATEADD({Date},6, 'day')

NOTE: Replace the field name Date with the name of your field.

EDIT: I retract this statement. I misread your initial request for it to be workdays only.

Nataliya_Bondarenko · June 4, 2019, 10:27am

So, should it work implementing workdays? Is any solutions exist? Thanks!

Nathalie_Collins · June 4, 2019, 10:29am

This one will work for just workdays.

WORKDAY({Date},6)

Nataliya_Bondarenko · June 4, 2019, 10:45am

Thank you very much!!! You are the BEST!

Beth_Gale · September 3, 2019, 9:56am

has this been more simplified? I have many projects I would like to use this formula on but the holidays and weekends will always be different? unless I add every single weekend date which would be a forever list…?

system · April 27, 2020, 10:03pm

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.