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

Asked By Claudi on 26-Jun-07 12:20 PM
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\) replied on 26-Jun-07 12:31 PM
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\) replied on 26-Jun-07 12:41 PM
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 replied on 26-Jun-07 12:42 PM
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
Claudi replied on 26-Jun-07 12:43 PM
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\) replied on 26-Jun-07 12:47 PM
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
Claudi replied on 26-Jun-07 12:58 PM
You get the crown!  This formula takes care of all the variables!
Thanks!
Claudi replied on 26-Jun-07 01:04 PM
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\) replied on 26-Jun-07 01:10 PM
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
drille replied on 26-Jun-07 01:12 PM
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..
drille replied on 26-Jun-07 01:15 PM
d21 contains the date (startdate+60days)

*****
birds of the same feather flock together..
Peo Sjoblom replied on 26-Jun-07 01:16 PM
Try it with 01/01/08


--
Regards,

Peo Sjoblom
Bernd P replied on 28-Jun-07 12:10 AM
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