Calculate first day of next month

[Sharon_Visser]

Hi Airtable Community,

For a payroll automation base I’m developing, I need to figure out how to set a payroll date which will be 30 days after the first of the next month or the first of 2 months out, depending on net terms.

For example, if someone logs time worked on 11/15/2020, and their payment terms are Net 30, the payment would be due to them by figuring out the first day of the next month, (12/1/2020), and adding 30 days which would be 12/31/2020.

If someone logs time worked on 11/15/2020, and their payment terms are Net 60, the payment would be due to them by figuring out the first day of the next month, (12/1/2020), and adding 60 days which would be 1/30/2021 (or so I think).

I’m trying to automate this calculation if possible. I’ve set up multiple calculation fields to take the number of the month, add 1, adjust if it’s past December. But then I have to address the year as well. So I wondered if there might be some kind of date formula to help with this, or if someone might have already figured out how to do this.

Thanks in advance.

[kuovonne · Accepted solution]

You can get the first day of the next month with this formula:

DATEADD(
  DATETIME_PARSE(YEAR({Date}) & "-" & MONTH({Date}) ,"YYYY-MM"),
  1,
  "months"
)

You can use another DATEADD formula to add the 30 or 60 days.

[Sharon_Visser]

Thank you so much! I will give this a try.

Much appreciated.