Calculating, filtering, and sorting 5 year anniversaries?
 

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will
be entitled to their 5th year award within the next 12 months...



"Marcelo" wrote:

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

Hi,

In my understanding you told that if the 5th year will be completed during
the current year you should accrual (or something like that) the awards.

right?

the datedif function has variations that you can use as
"Y" - Completed years
"M" - Completed months
"D" - Days
"MD" - diference between the dates but months and years are ignorated
"YD" - diference between the dates but days and years are ignorated

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will
be entitled to their 5th year award within the next 12 months...



"Marcelo" wrote:

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

No, I need to identify those anniversaries that will occur within the next 12
months, starting from today. I'm wondering now if I can "bracket" these
dates by using your formula for one, and use a variation where I add 12
months for the other...

"Marcelo" wrote:

Hi,

In my understanding you told that if the 5th year will be completed during
the current year you should accrual (or something like that) the awards.

right?

the datedif function has variations that you can use as
"Y" - Completed years
"M" - Completed months
"D" - Days
"MD" - diference between the dates but months and years are ignorated
"YD" - diference between the dates but days and years are ignorated

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will
be entitled to their 5th year award within the next 12 months...



"Marcelo" wrote:

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

Ricter

what about

=datedif(hiredate,today()+365,"y")

if it return 5, 10, 15, etc.. are the anniversaries

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

No, I need to identify those anniversaries that will occur within the next 12
months, starting from today. I'm wondering now if I can "bracket" these
dates by using your formula for one, and use a variation where I add 12
months for the other...

"Marcelo" wrote:

Hi,

In my understanding you told that if the 5th year will be completed during
the current year you should accrual (or something like that) the awards.

right?

the datedif function has variations that you can use as
"Y" - Completed years
"M" - Completed months
"D" - Days
"MD" - diference between the dates but months and years are ignorated
"YD" - diference between the dates but days and years are ignorated

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will
be entitled to their 5th year award within the next 12 months...



"Marcelo" wrote:

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia

Calculating, filtering, and sorting 5 year anniversaries?
 

Here's what I've settled on Marcelo, since the user indicates that the
anniversaries are based on the calendar year after all (much easier):

A1: =2006-YEAR(HireDate)
and
B1: =IF(A1/5=INT(A1/5), "Yes", "No")

I then do sorts on Column B then Column A to get all the "Yes" together and
those sorted by which 5th year anniversary it is.

Thanks for your "seed" ideas!

"Marcelo" wrote:

Ricter

what about

=datedif(hiredate,today()+365,"y")

if it return 5, 10, 15, etc.. are the anniversaries

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

No, I need to identify those anniversaries that will occur within the next 12
months, starting from today. I'm wondering now if I can "bracket" these
dates by using your formula for one, and use a variation where I add 12
months for the other...

"Marcelo" wrote:

Hi,

In my understanding you told that if the 5th year will be completed during
the current year you should accrual (or something like that) the awards.

right?

the datedif function has variations that you can use as
"Y" - Completed years
"M" - Completed months
"D" - Days
"MD" - diference between the dates but months and years are ignorated
"YD" - diference between the dates but days and years are ignorated

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

Gonna have to get a bit more complicated than that methinks. An employee
with a hire date of 1/1/2002 will result in 4 years of service, but they will
be entitled to their 5th year award within the next 12 months...



"Marcelo" wrote:

hi

=datedif(hiredate,todaydate,"Y")

it will return you the difference in completed years

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Ricter" escreveu:

If I have an employee database with hire dates, and a company longevity award
program that gives an award for each 5th year anniversary, ie. 5, 10, 15,
etc. years of service, what formula could I use to flag every record/row
where an employee is entitled to an award, or not, in the coming 12 months?

tia