Hello I am a brand new user and I don't speak english very well.

so bare with me.

I am creating a database for Employee Service

and I am having trouble getting my head around the calculation concept. If any of you could help out I would really appreciate it.

I have these Field

DOB ( Date of Birthday )

DOJ ( date of Join )

EMP_Serv = calculation

EMP_Ret = calculation

and ofcourse Current date

How to find the date of service( from current date ) if the Employee completed ( 30 ) years of servece.

And how to find the date of Employee Retirement ( from current date )

( if he reach 55 years )

Which will come first when he completed 30 years of service or when he reach 55 years old.

I hope you understand what I mean, Please HELP !!!

Well this should give you a rough start. It is not perfect but it might get you headed in the right direction.... i think.

Case

(

((55 -  (Get ( CurrentDate ) - DOB)) <  (30 -  ( Get ( CurrentDate ) - DOJ ) ) ;  //If the person will be 55 before they complete 30 years of service



DOB + (365 * 30) ; // Date of birth plus 30 years.   THis is not quite right because you will have to figure for leap year.



//Otherwise it is 30 years of service



DOJ + (365 * 30) // Which again is not quite accurate due to leap year.



)

I am not sure what exactly you're asking, so let me give you a few options to pick from.

1. The number of (whole) years a person has been employed:

Let ( 

today = Get (CurrentDate)

;

Year ( today ) - Year ( DOJ)  - ( Today < Date ( Month ( DOJ ) ; Day ( DOJ ) ; Year ( today ) ) )

)

This calculation returns a Number, and it must be unstored.





2. The date on which a person will be 55 years old:

Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( DOB ) + 55 )

3. The date on which a person will have completed 30 years of employment:

Date ( Month ( DOJ ) ; Day ( DOJ ) ; Year ( DOJ ) + 30 )

Nippon,

EMP_Serv =

Get ( CurrentDate ) - DOJ

EMP_Ret =

Get ( CurrentDate ) - DOB

These two calculations will return the number of days between today and the date of join / birth, respectively.

To display the earliest date, create a calc field that returns date with this formula =

Min ( EMP_Serv; EMP_Ret )

To display which date is earlier, create a calc field that returns text with this formula =

Case (

EMP_Serv < EMP_Ret; "30 Years Service before Retirenment";

EMP_Ret < EMP_Serv; "Retirement before 30 Years Service"

EMP_Ret = EMP_Serv; "Retirement and 30 years service on same day."

)

Make sure that EMP_Serv and EMP_Ret are unstored calculations since any calculation field that calls Get ( CurrentDate ) must be unstored. Also make sure EMP_Serv and EMP_Ret are calculations that return numbers.

Edited June 16, 200916 yr by Guest

I wonder what this part is supposed to do:

55 -  (Get ( CurrentDate ) - DOB)

EMP_Serv =

Get ( CurrentDate ) - DOJ

EMP_Ret =

Get ( CurrentDate ) - DOB

...

Case (

EMP_Serv < EMP_Ret; "30 Years Service before Retirenment";

EMP_Ret < EMP_Serv; "Retirement before 30 Years Service"

EMP_Ret = EMP_Serv; "Retirement and 30 years service on same day."

)

I believe the first condition will be always true - unless a person started working for the company before (or on the same day) they were born.

As I said not quite right but the general idea. What I needed to finish was converting the difference of the DOB and the current date to number of years.

I don't think the "general idea" is a good approach to take here. If a person needs to retire at age 55 or at 30 years of employment, whichever comes first, the date of retirement can be computed from DOB and DOJ alone:

Min (

Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( DOB ) + 55 ) ; 

Date ( Month ( DOJ ) ; Day ( DOJ ) ; Year ( DOJ ) + 30 )

)

This does not change with time passing, and Get (CurrentDate) can (and should) be eliminated from the calculation.

Thank you all for your help. I tried all the code you gave me. but all the return value is a number.

What I really want the output is a date.

If some one can attach an example file please

The result of the comment's calculation IS a date, providing that you set "date" as the result of the calc, not "number".

I don't think the "general idea" is a good approach to take here.

I see...