I have a database that keeps track of employees sick, vacation and personal dates, I need to sum the total of these fields, can I calculate the sum off this formula?
=IIf([datediff]<1825,6.66666666666,IIf([datediff] Between 1826 And 3650,10,IIf([datediff]>="3651",13.33333333333,"")))

I'm not sure I'm explaining this right, so if you need more info, please let me know.

Thank you.

I have written a number of vacation benefits databases. Is this what you are trying to do:

If the employee has less than 5 years service then return 6.666666
If the employee has 5-10 years service return 10
If the employee has more than 10 years service return 13.3333

I assume the values returned will represent the allowable benefit for the employee:6 2/3 days, 10 days, 13 1/3 days...?

Yes, but the hours they earned is based on the date of hire, for instance if they were hired in July 2001, they would have earned 6.667 for the months of January - June and 10 for July and all the months forward until they reach July 2011. I have the formula right and it's calculating the right hours earned, but I need to total the field to be able to deduct any hours they have fromt he previous year. I created a report using subreports. The main report shows the hours they have from the previous year and one subreport shows hours earned and the other subreport shows dates and hours used. I need to calculate the subreport from the MTD and sutract it from the field in the main report showing previous years hours earned.

Did this help?

Vacation Benefits Rules can be quite complex especially if dealing with multiple Union Contracts. Most companies operate off of a Benefits Year, be it the Employee's Hire Date, Seniority Date, Department StartDate, or some other fiscal Fixed Date like January 1st or July 1st, whatever. You state that your company uses the Employee Hire Date.

In your above example then, you are correct that the employee would be entitled to 6.6667 days up until the end of June 2006. On the employee's Hire Date of July 1st the employee's entitlement bank would be recalculated to now reflect a new benefit of 10 days.

If you already have sub reports displaying the necessary data and it is all calculating properly, what is preventing you from building a query joining the employee's Total Available Benefits Bank and the Total Usage-To-Date, and Calculating the Employee Balances?

I think I am missing what you are asking.