I am trying to calculate benefit eligibility dates.

dorypluger
dorypluger ✭
in Formulas and Functions

I need to calculate the 1st of the month following 60 days of employment.

Ex. If a hire date is 1/15/2024, then I would need the formula to return 4/1/2024 (60 days after 1/15/24 is 3/15/24, so April 1st would be the date they are eligible for benefits).

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/14/24 Answer ✓

    @dorypluger

    Is all the information on the same sheet or seperates?

    If so This works.

    =IF(DAY([60 Days]@row) = 1, [60 Days]@row, IFERROR(DATE(YEAR([60 Days]@row), MONTH([60 Days]@row) + 1, 1), DATE(YEAR([60 Days]@row) + 1, 1, 1)))

    If you do not want it to count if the date falls on the 1st then just remove that If statement.

    =IFERROR(DATE(YEAR([60 Days]@row), MONTH([60 Days]@row) + 1, 1), DATE(YEAR([60 Days]@row) + 1, 1, 1)))

    as shown in the final row of the screenshot.

    image.png

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/14/24 Answer ✓

    @dorypluger

    Is all the information on the same sheet or seperates?

    If so This works.

    =IF(DAY([60 Days]@row) = 1, [60 Days]@row, IFERROR(DATE(YEAR([60 Days]@row), MONTH([60 Days]@row) + 1, 1), DATE(YEAR([60 Days]@row) + 1, 1, 1)))

    If you do not want it to count if the date falls on the 1st then just remove that If statement.

    =IFERROR(DATE(YEAR([60 Days]@row), MONTH([60 Days]@row) + 1, 1), DATE(YEAR([60 Days]@row) + 1, 1, 1)))

    as shown in the final row of the screenshot.

    image.png

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter
  • dorypluger
    dorypluger ✭
    edited 05/15/24

    @Mark.poole

    This looks great!

    I will make one modification for December. When month is 12, force the benefit month to be 1.

    Thank you so much!!!

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/435946#Comment_435946

    You are very welcome

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!