Employees are eligible for benefits the first day of the month after 60 days
of work. Example: An employee starts working today 6/26/07; her benefits
are effective September 1st., which is the first day of the month after 60
days of work. What formula I can use to come up with the effective date for
benefits?

Employees are eligible for benefits the first day of the month after 60
days
of work. Example: An employee starts working today 6/26/07; her benefits
are effective September 1st., which is the first day of the month after 60
days of work. What formula I can use to come up with the effective date
for
benefits?

Assuming if 60 days hence ends up on the first of the month, you use that
date...

=IF(DAY(A1+60)=1,A1,DATE(YEAR(A1+60),1+MONTH(A1+60 ),1))

Change the A1 reference to the cell where your date is.

Rick

Assuming if 60 days hence ends up on the first of the month, you use that
date...

=IF(DAY(A1+60)=1,A1,DATE(YEAR(A1+60),1+MONTH(A1+60 ),1))

Change the A1 reference to the cell where your date is.

Whoops! I left out a +60. Try this formula....

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Rick

Hello Claudia,

=DATE(YEAR(A1+60),MONTH(A1+60)+1,1)
might be what you are looking for.

60 days of work would literally be 60 working days:
=DATE(YEAR(WORKDAY(A1,60)),MONTH(WORKDAY(A1,60))+1 ,1)
[you need the Analysis Toolpak for this: goto Tools/Addins and tick
that box]
but I guess you do not mean it this way. It would be 1-Oct for
today...

Regards,
Bernd

Hi Rick,
The formula works well for most dates except the ones on the second day of
the month (i.e., 5/2/07, 6/2/07, 7/02/07) because it gives you back their own
value. What do you think?

"Rick Rothstein (MVP - VB)" wrote:

Employees are eligible for benefits the first day of the month after 60
days
of work. Example: An employee starts working today 6/26/07; her benefits
are effective September 1st., which is the first day of the month after 60
days of work. What formula I can use to come up with the effective date
for
benefits?

Assuming if 60 days hence ends up on the first of the month, you use that
date...

=IF(DAY(A1+60)=1,A1,DATE(YEAR(A1+60),1+MONTH(A1+60 ),1))

Change the A1 reference to the cell where your date is.

Rick

The formula works well for most dates except the ones on the second day of
the month (i.e., 5/2/07, 6/2/07, 7/02/07) because it gives you back their
own
value. What do you think?

Yeah, I saw that and posted a correction... here the correction again...

I left out a +60. Try this formula....

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Rick

You get the crown! This formula takes care of all the variables!
Thanks!

"Rick Rothstein (MVP - VB)" wrote:

Assuming if 60 days hence ends up on the first of the month, you use that
date...

=IF(DAY(A1+60)=1,A1,DATE(YEAR(A1+60),1+MONTH(A1+60 ),1))

Change the A1 reference to the cell where your date is.

Whoops! I left out a +60. Try this formula....

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Rick

Hi Bernd P
I tried your formula, but it does not work for people who are hired on the
first or second day of the month. The formula that Rick Rothstein (MVP - VB)
sent takes care of those dates as well.

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Thank you for being willing to help!


"Bernd P" wrote:

Hello Claudia,

=DATE(YEAR(A1+60),MONTH(A1+60)+1,1)
might be what you are looking for.

60 days of work would literally be 60 working days:
=DATE(YEAR(WORKDAY(A1,60)),MONTH(WORKDAY(A1,60))+1 ,1)
[you need the Analysis Toolpak for this: goto Tools/Addins and tick
that box]
but I guess you do not mean it this way. It would be 1-Oct for
today...

Regards,
Bernd

To be fair to Bernd, I guessed one way and he guessed the other. You didn't
say how you wanted to handle 60 days hence landing on the first of a month
and one could read your Subject as indicating you wanted to bump it to the
next month (what Bernd's formula does).... the word "after" is what creates
the uncertainty.

Rick



"Claudia" wrote in message
...
Hi Bernd P
I tried your formula, but it does not work for people who are hired on the
first or second day of the month. The formula that Rick Rothstein (MVP -
VB)
sent takes care of those dates as well.

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Thank you for being willing to help!


"Bernd P" wrote:

Hello Claudia,

=DATE(YEAR(A1+60),MONTH(A1+60)+1,1)
might be what you are looking for.

60 days of work would literally be 60 working days:
=DATE(YEAR(WORKDAY(A1,60)),MONTH(WORKDAY(A1,60))+1 ,1)
[you need the Analysis Toolpak for this: goto Tools/Addins and tick
that box]
but I guess you do not mean it this way. It would be 1-Oct for
today...

Regards,
Bernd

also with analysis tool pack-addin

=IF(D21=EOMONTH(D21,-1)+1,D21,EOMONTH(D21,0)+1)

regards,
driller
--
*****
birds of the same feather flock together..



"Bernd P" wrote:

Hello Claudia,

=DATE(YEAR(A1+60),MONTH(A1+60)+1,1)
might be what you are looking for.

60 days of work would literally be 60 working days:
=DATE(YEAR(WORKDAY(A1,60)),MONTH(WORKDAY(A1,60))+1 ,1)
[you need the Analysis Toolpak for this: goto Tools/Addins and tick
that box]
but I guess you do not mean it this way. It would be 1-Oct for
today...

Regards,
Bernd

d21 contains the date (startdate+60days)

*****
birds of the same feather flock together..



"driller" wrote:

also with analysis tool pack-addin

=IF(D21=EOMONTH(D21,-1)+1,D21,EOMONTH(D21,0)+1)

regards,
driller
--
*****
birds of the same feather flock together..



"Bernd P" wrote:

Hello Claudia,

=DATE(YEAR(A1+60),MONTH(A1+60)+1,1)
might be what you are looking for.

60 days of work would literally be 60 working days:
=DATE(YEAR(WORKDAY(A1,60)),MONTH(WORKDAY(A1,60))+1 ,1)
[you need the Analysis Toolpak for this: goto Tools/Addins and tick
that box]
but I guess you do not mean it this way. It would be 1-Oct for
today...

Regards,
Bernd

Try it with 01/01/08


--
Regards,

Peo Sjoblom



"Claudia" wrote in message
...
You get the crown! This formula takes care of all the variables!
Thanks!

"Rick Rothstein (MVP - VB)" wrote:

Assuming if 60 days hence ends up on the first of the month, you use
that
date...

=IF(DAY(A1+60)=1,A1,DATE(YEAR(A1+60),1+MONTH(A1+60 ),1))

Change the A1 reference to the cell where your date is.

Whoops! I left out a +60. Try this formula....

=IF(DAY(A25+60)=1,A25+60,DATE(YEAR(A25+60),1+MONTH (A25+60),1))

Rick

Hello Claudia,

Do you really intend different treatment of let's say 2-Feb-2008 and 2-
July-2007?

Compare Rick's formula with this one:
=DATE(YEAR(A1+60),MONTH(A1)+3-(DAY(A1)=1),1)

Input Rick's output Mine
2-July-2007 1-September-2007 1-October-2007
2-Feb-2008 1-May-2008 1-May-2008

It's just to understand you correctly.

Regards,
Bernd