add 2 months and a variable number of days to a date

T

Tim Nealon

I am trying to calculate a health benefits schedule. I would like to know
the month an employee becomes eligible for health benefits. An employee is
eligible on the first day of the month following 2 months of employment.
For example, if an employee is hired anytime during the month of September,
he would be eligible for benefits on December 1. If he is hired in October,
he would not be eligible until January 1 of the next year.
My hire date is in MM/DD/YY format. Does anyone know how to calculate the
"eligible for benefits" date?
Thanks
 
H

Harald Staff

Hi

If I understand your description, with date in A1:
=DATE(YEAR(A1),MONTH(A1)+3,1)

If one is hired the 1st then I'm not sure what you want. Post back if wrong
result.

HTH. Best wishes Harald
 
S

Sasa Stankovic

hy,

this are dates whis I put in column A:

20.09.2007

01.04.2007

15.11.2007

10.01.2007

11.04.2007

25.12.2007

I use dd.MM.yyyy format (it regular format in my country).

In column B I put next formula:

=VALUE(IF(MONTH(A1)<11;IF(MONTH(A1)+2<=12;"01."&MONTH(A1)+2&"."&YEAR(A1);"01."&MONTH(A1)+2&"."&YEAR(A1)+1);IF(MONTH(A1)=11;"01."&"01"&"."&YEAR(A1)+1;"01."&"02"&"."&YEAR(A1)+1)))

Please try it and tell me if you understand. If not I'll try to translate in
your MM/DD/YY form.

Bye,

sasa
 
R

Rick Rothstein \(MVP - VB\)

For example, if an employee is hired anytime during the month of
September, he would be eligible for benefits on December 1.

Just to be clear... is December 1st the "benefits day" even if the start
date is September 1st?

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top