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

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

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

It’s not working for me… I am so disappointed! HELP!!!


I simply need +6 workdays from Date of Request…

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

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

This one will work for just workdays.

WORKDAY({Date},6)

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

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