FIRST OF THE MONTH AFTER 60 DAYS

ANA G

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!

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!