FIRST OF THE MONTH AFTER 60 DAYS

I am trying to auto populate effective dates for benefits and I cannot get the calculation to work past the first part (60 days after start date)

I need to have the following result as an example

Start date: 1/15/2020

Effective Date: 4/1/2020

Thanks!

Find more posts tagged with

Human Resources

Formulas

Media & Entertainment

Comments

Andrée Starå

Hi Ana,

Try something like this.

=DATEONLY([Start Date]@row + 60)

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Andree,

I think they are trying to populate the 1st of whatever month is 60 days out.

If that is the case, I would suggest something like this...

=DATE(YEAR([Start Date]@row + 60), MONTH([Start Date]@row + 60), 1)

EDIT to show the first of the month FOLLOWING 60 days and to account for overlap into the following year...

=IFERROR(DATE(YEAR([Start Date]@row + 60), MONTH([Start Date]@row + 60) + 1, 1), DATE(YEAR([Start Date]@row + 60) + 1, MONTH([Start Date]@row) - 9, 1))

Andrée Starå

Paul,

Yes, you're correct! I missed that part.

Thanks!

Margaret Conklin

I'm using this formula for the same reason, benefit eligibility, however, if the hire date is the first day of the month, this formula won't give the correct date. For example, if the hire date is March 1, it will give an eligibility date of May 1, when it needs to be June 1. Do you have a way to correct this?

Thanks!

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…