### Title

Calculations using dates, help

### Post

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?

There are various date functions in FMP. The handiest one for you would be :

DayOfYear (date)which returns the number of days since January 1 (which would = 1 on January 1st, to answer that part).(You need to use 4-digit dates for date functions to work properly.)

There is also the function :

Year (date)which returns the numeric value of the year from the date, e.g. the number 2011.Using both of these you can set up Calculation fields to return the Vacation hours entitlement for example.

For the automated operations, I'm guessing you need a Calculation using Functions that will - if a particular value is returned - run a script to reset the value of given fields. However, you would need someone who has done that to confirm if it's possible. (I've written and run scripts; and I've defined many a complex Calculation, but I've not done both together).