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

[W_Vann_Hall]

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. :winking_face:

[W_Vann_Hall]

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

[Jeremy_Chevalli]

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]

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_Chevalli]

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]

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_Chevalli]

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]

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

[Nataliya_Bondar]

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

[Nathalie_Collin]

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