How to calculate a date: first day of the month after 60 days

[Claudia]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bernd P]

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

 

[Claudia]

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\)]

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

 

[Claudia]

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

 

[Claudia]

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!

 

[Rick Rothstein \(MVP - VB\)]

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

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!

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

 

[driller]

also with analysis tool pack-addin

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

regards,
driller

 

[driller]

d21 contains the date (startdate+60days)

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

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..

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

 

[Peo Sjoblom]

Try it with 01/01/08

 

[Bernd P]

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