I am trying to calculate benefit eligibility dates.

dorypluger

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

Mark.poole

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

Mark.poole

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

dorypluger

@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

https://community.smartsheet.com/discussion/comment/435946#Comment_435946

You are very welcome