# Future Date Formula

Topic Labels: Formulas
Solved
1679 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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.

1 Solution

Accepted Solutions
18 - Pluto

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:

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:

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:

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:

I left the time option active for the field formatting, but you can obviously turn that off if you wish.

2 Replies 2
18 - Pluto

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:

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:

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: