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

count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…