I need to calculate service period

[warning99]

HI,

I have table contain field for date of join. And I want put formula field calculate service period for all employees.

The date in this format YYYY-MM-DD

the equation is:

First 5 year :
---------------------------------------
(No.of days/30) * 1.25 * (salary/30)
---------------------------------------


After 5 years:
---------------------------------------
(No.of days/30) * 2.5 * (salary/30)
---------------------------------------



For Example:

The salary for employee is 1070 and date of join is 24/3/1989

1-first 5 years
from 24/3/1989 to 23/3/1994 = 5 years = 1800 days

(1800/30) * 1.25 * (1070/30) = 2675

2- after 5 years
from 24/3/1994 to 30/06/2009 =5497 days

(5497/30) * 2.5 * (1070/30) = 16338

Tot=16338+2675=19013

the furmula should show 19013

plz help me

 

[IanWaterman]

YOu can use dateadd and datediff look them up in help for use.

In your formula first test to see if start date within 5 years

If dateadd("yyyy", 5 {startdatefield}) >=currentdate then

datediff("d", {startdatefield}, currentdate)*2.5*(salaryfield/30)

else

using same logic build the rest of your formula.


Ian

 

[warning99]

Thank you

But if the employee work 7 years. Your code will use the second rule for all 7 years.

To be perfect, The Formula should calculate first 5 years with first rule and tow years with the second rule.

thx

 

[IanWaterman]

Sorry my mistake in first part should be

If dateadd("yyyy", 5 {startdatefield}) >=currentdate then

datediff("d", {startdatefield}, currentdate)*1.25*(salaryfield/30)

else


The else will have two parts

datediff("d", {startdatefield}, dateadd("yyyy", 5 {startdatefield}))*1.25*(salaryfield/30)

+

datediff("d", dateadd("yyyy", 5 {startdatefield}, currentdate)*2.5*(salaryfield/30)

Ian