I’m trying to use a formula field to calculate a date for benefits eligibility like health insurance. For example, if an employee starts on August 6th, 2020 we require them to wait until the first of the month following their first 90 days of employment until they are eligible.
With the DATEADD formula, I can add 90 days, but I’m not sure how to take the result from 90 days to the first of the following month.
With the example above the result I’m looking for would be December 1st, 2020.
Welcome to the community, @Cameron_Mason! :grinning_face_with_big_eyes: You’ve got a good start with DATEADD(). Now it’s just a matter of adding other pieces to it. To get the next month after that 90-day window, add one month to the result of your initial calculation:
DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")
Using your example starting date, that will put the date somewhere in December. To get the actual month as a number, wrap the MONTH() function around it:
MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months"))
That returns the number 12. Doing something similar with the YEAR() function to grab the year, and adding “/1/” in the middle of the two, we can build a string that represents the first date of that month:
MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")) & "/1/" & YEAR(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months"))
That will give us the string “12/1/2020” in this example. Now wrap the DATETIME_PARSE() function around all of it, and you have an actual date:
DATETIME_PARSE(MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")) & "/1/" & YEAR(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")), "M/D/YYYY")
I left the time option active for the field formatting, but you can obviously turn that off if you wish.
Welcome to the community, @Cameron_Mason! :grinning_face_with_big_eyes: You’ve got a good start with DATEADD(). Now it’s just a matter of adding other pieces to it. To get the next month after that 90-day window, add one month to the result of your initial calculation:
DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")
Using your example starting date, that will put the date somewhere in December. To get the actual month as a number, wrap the MONTH() function around it:
MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months"))
That returns the number 12. Doing something similar with the YEAR() function to grab the year, and adding “/1/” in the middle of the two, we can build a string that represents the first date of that month:
MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")) & "/1/" & YEAR(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months"))
That will give us the string “12/1/2020” in this example. Now wrap the DATETIME_PARSE() function around all of it, and you have an actual date:
DATETIME_PARSE(MONTH(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")) & "/1/" & YEAR(DATEADD(DATEADD({Start Date}, 90, "days"), 1, "months")), "M/D/YYYY")
I left the time option active for the field formatting, but you can obviously turn that off if you wish.
Thank you Justin! This was super helpful. I use Airtable mainly for linking data and tracking progress, most of my modeling and formulas are done in Google Sheets.
I was obviously having trouble figuring this out in Airtable.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.