Anniversary Date

[Norman Harker]

Hi djeans!

Re the >= 29 problem..

For 31-Aug-2000 start date the next 6 monthly date will be returned as
2-Mar-2004. But after 2-Mar-2004 it will be returned as 31-Aug-2004
and after 31-Aug-2004 it will be returned as 3-Mar-2005. It's not so
much an error as a definitional difference.

Most people, (EDATE and Edit > Fill > Series) would prefer a return of
the last date of the month 6 months hence if the DoM of base month
doesn't exist. The problem gets worse if you try to copy down a series
of equally spaced time periods.

Typical way of adding months is to use:
Where AddMons is the number of months to be added:
=DATE(YEAR(A1),MONTH(A1)+AddMons,DAY(A1)
Try that with 31-Aug-2003 with different AddMons and copy each down
awhile and you'll see the problem.

One solution is:
=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH
(A1)+1+AddMons,0))))

In an earlier reply I found that I ran out of allowable formula length
using that approach. Peter Dorig's approach is more efficient:
=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))

However, I don't think that these super long formulas are a good idea
if the use of helper columns is possible. Hence my alternative
solution earlier in this thread.

For calculation of years and months of employment use:

=DATEDIF(Start_Date,End_Date,"y")&" years
"&DATEDIF(Start_Date,End_Date,"ym")&" months"

Best way of entering dates is by cell references to cells that contain
dates.

See Chip Pearson's site for the details of the mysterious Excel
Built-In function DATEDIF (and a whole lot more):

http://www.cpearson.com/excel/datedif.htm


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

I have gone with the helper column idea and hid the helper colums s
that they don't confuse myself or anyone else who may use the page.

You have all been a great help.

I thank you ALL.

Darro

 

[Norman Harker]

Hi Darron!

Pleased to help and hear that it's all working OK.

Keep posting! That next 6 monthly anniversary question has quite wide
applications and I've not tried to solve it before. As an example, we
might use it for determining the next rental payment date based upon a
lease commencement date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

I have the spreadsheet set up using the helper columns, but there seems
to be something funny going on.

All of the 6 month review dates are listed as being on the same day of
the month, regardless of the hire day.

For instance, Hire date of 8/18/03 shows the next review being as 2/2,
and hiredate of 8/26/03 shows next review as 2/2 also.

Is this correct based on the helper columns?

It's not a major issue, but just seems odd.

Darron

 

[djeans]

Maybe I have something misconfigured.

A2 = last name
B2 = First name
C2 = Hiredate
D2 = Today()
E2 =IF(D2<
DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE(YEAR(D2)+1,MONTH(C2),DAY(C2)))

F2
=DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2)-5,0))))

G2
=IF(DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2)-5,0))))>D2,MIN(E2,F2),E2)

With E2 and F2 being the helper columns, and G2 being the next review
date.

Do I have it right?

darron

 

[Norman Harker]

Hi Darron!

I've checked your formulas against mine and they are exactly the same.
So you have a difference on your workbook somewhere.

The day of the date for the review is taken from the date of hire
except where that date >=29 in which case it is the last day of the
month if the hire day doesn't exist.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

enter in several hire dates in the august - december range

Let me know if you get review dates that are all the same day in each
month, or if they are different.

darron

 

[Norman Harker]

Hi Darron!

All dates have same DoM as the hire date. Would you like a copy of my
test workbook?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

That might help me figure out what the problem is.

thanks

darro

 

[Norman Harker]

Hi Darron!

Send email to address below and I'll reply with attachment.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.