I created a database for the office to track the details of the
employees, including start/hire dates. The HR Department wants to
project the anniversary dates of the employees and I did it using the
following calculation

If(DateofHire + 1915 <= Today,"Ten Year Anniversary: " &
(DateToText(DateofHire + 3650)),If(DateofHire + 1185 <= Today,"Five
Year Anniversary: "&(DateToText(DateofHire + 1825)),If(DateofHire + 450
<= Today,"Three Year Anniversary: " & (DateToText(DateofHire +
1095)),If(DateofHire + 90 <= Today,"One Year Anniversary: " &
(DateToText(DateofHire + 365)), "Probationary"))))

The anniversary dates are as follows
1 YR
3 YR
5 YR
10 YR

In order to display the projected anniversary date I had to add 365
days to the hire date or a multiple of 365 for the other years. The
challenge comes from the fact the there are not 365 days in every year
and an anniversary date should fall on the same date - 12/09/2000 to
12/09/2005. adding 365 days is inconsistent and changes the date.

Finally, my question - how do I change only the year and not the date?

Year3 = Date( Month(DateofHire), Day(DateofHire), Year(DateofHire) + 1)

Year3 = Date( Month(DateofHire), Day(DateofHire), Year(DateofHire) + 3)

and so on.

Matt

Copyright © 1994 - 2002 FileMaker, Inc. All Rights Reserved.

sbitaxi (AT) yorku (DOT) ca wrote:

Sorry, that first calc field name should be Year1

Matt Wills wrote:

You can split the DateOfHire into day, month and year (as numbers) and
re-use them.
cDay = day ( DateOfHire )
cMonth = month ( DateOfHire )
cYear = year ( DateOfHire )

Then add 1 (or whatever you need) to the year and put them back together as
date

cAnniversary = Date ( cMonth ; cDay ; cYear+1 )

If ( today = cAnniversary ; "HURRAY!" ; "Just wait...")

with FM7 use Get ( CurrentDate) instead of Today

<sbitaxi (AT) yorku (DOT) ca> schreef in bericht
news:1120500796.116354.68320 (AT) g47g2000cwa (DOT) googlegroups.com...

In article <42ca5264$0$89655$dbd4b001 (AT) news (DOT) wanadoo.nl>, "ursus.kirk"
<secret (AT) nowhere (DOT) com> wrote:

NEVER use Today.

Always use Status(CurrentDate) before FileMaker 7 and Get(CurrentDate)
with FileMaker 7.

Like Repeating Fields, the Today function is a left-over that should
really be removed. Today is only calculated ONCE when FileMaker is run,
so if running on a server (for example) or left running over-night the
Today function will give an incorrect date. (Obviously there are
occasionally times when knowing the "last started up date" is useful.)

The Status / Get(CurrentDate) function on the other hand is calculated
EVERY time it is used and so always gives the correct date.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thanks Harry, I had no idea. Today is now yesterday.


S

Just to be clear -

this will result in separate fields for each anniversary date, plus one
which will calculate the one that is coming up?

Admittedly, I want to do everything that I possibly can with one field,
but a couple extra won't really hurt me that much ; )

that's easy. The only issue I see is the time frame around the
anniversary date, the only way that cAnniversary would display the next
coming date, is if it was already here.

Hence my really convoluted IF this then that so add something else and
say the alphabet backwards equation. Parsing out the date has great
potential.


S

my "Just to be clear" reply is to Matt and the "that's easy" is to
ursus.kirk.

Thanks to everyone for their expertise. Someday, I may have an insight
to share with you too! ; )


S

Steven Bitaxi wrote:

This one seems to do everything in one field (calc is text):

Anniversary =

Case(
Status( CurrentDate) >= Date( Month(DateofHire), Day(DateofHire),
Year(DateofHire) + 10), "Tenth Anniversary: " & DateToText(Date(
Month(DateofHire), Day(DateofHire), Year(DateofHire) + 10)),
Status( CurrentDate) >= Date( Month(DateofHire), Day(DateofHire),
Year(DateofHire) + 5), "Fifth Anniversary: " & DateToText(Date(
Month(DateofHire), Day(DateofHire), Year(DateofHire) + 5)),
Status( CurrentDate) >= Date( Month(DateofHire), Day(DateofHire),
Year(DateofHire) + 3), "Third Anniversary: " & DateToText(Date(
Month(DateofHire), Day(DateofHire), Year(DateofHire) + 3)),
Status( CurrentDate) >= Date( Month(DateofHire), Day(DateofHire),
Year(DateofHire) + 1), "First Anniversary: " & DateToText(Date(
Month(DateofHire), Day(DateofHire), Year(DateofHire) + 1)),
"Probationary")

Matt