I am trying to calculate benefit eligibility dates.

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).

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.

    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.

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.

    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.

  • 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!!!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!