Calculations using dates, help
FMP10 on PC and MAC
We have a FMP file that employees use in a department that keeps track of their hours worked during a pay period. Pay period starts on Thursday mornings and ends on the following Wednesday, at the end of the day. We use this to keep track of employees personal time and vacation time. Here is where I need help.
1) Employees' personal time resets every January 1 of every year. The maximum amount anyone can ever receive is 40 hours. How can I get FMP to automatically reset the personal time on January 1 of every year? I keep track of personal hours used, so I would just need this to go to zero on January 1 of every year, how can I do this automatically? Personal hours do not carry over to the next year.
2) Employees' vacation time resets on the anniversary date of when they started. I already have every employee's starting date. If an employee started working on March 8, 2009, how can I get the employee's vacation time to reset on every March 8th?
3) The amount of vacation time varies. Here is the breakdown:
After 1 Year of Service (from employee's starting date): 40 Vacation Hours (this is 5 vacation days)
After 2 Years of Service (from employee's starting date): 80 Vacation Hours
After 5 Years of Service (from employee's starting date): 120 Vacation Hours
This is the general rule, but sometimes new employees can start with 40 or 80 vacation hours so I need to be able to modify the vacation time if necessary. How can I get FileMaker to automatically update the available vacation time based on CurrentDate - StartingDate ? And will I be able to override this and just enter a number for some employees? I keep track of vacation hours used, so I need this to go to zero on the anniversary of the employees' starting date, how can I do this automatically? Vacation hours do not carry over after the anniversary of the employee's starting date.
Does this make sense? Thoughts?