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
-
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
-
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.
-
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!!!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!