Help

Formula - add 7 days excluding Sundays and Holidays

Topic Labels: Formulas
1544 2
cancel
Showing results for 
Search instead for 
Did you mean: 
SF
4 - Data Explorer
4 - Data Explorer

I need to create a formula that will add 7 days to a manually entered date excluding Sundays and Holidays.

--- This is an item that gets mailed, and I need to mail it 7-days before the "due date." Hence, no Sundays or Holidays.

Any help or a point in the right direction would be most appreciated!

2 Replies 2
joshsorenson
6 - Interface Innovator
6 - Interface Innovator

Airtable does have a `WORKDAY()` function that allows you to calculate a date that is a certain number of workdays from a start date, excluding weekends (Saturday and Sunday) and, optionally, a list of dates that should be excluded (such as holidays).

Here's an example of how you might use the `WORKDAY()` function to solve your problem:

1. Create a field `Start Date` where you will input the initial date.
2. Create a list of holidays in a field, entering dates in a format separated by commas.
3. Create a final field, `End Date` with the formula:

WORKDAY({Start Date}, -7, {Holiday List})

The `-7` is used because the `WORKDAY()` function will calculate a future date if given a positive number and a past date if given a negative number. Since you want to mail the item 7 days before the due date, you need to use `-7` to get the correct date. This formula will return a date that is 7 workdays before the start date, excluding weekends and the holidays that you specified.

I was just trying to figure out the holiday list part and you figured it out! Do you have a sample airtable I can reference? I'm trying to figure out the response time of support inquiries from various companies but wanted to exclude weekends and holidays.