Calculate first day of next month

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.

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.

1 Like

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

Much appreciated.

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