Excel help

Clarifications

I'm not quite sure I follow you.

Could you give a couple of examples of what it should show? And maybe the calculation you currently have?

In plain text, that would be VacationAvailable=VacationPerYear-VacationUsed.

VacationPerYear = 5 days if TimeInEmploy < 5 or 8 if TimeInEmploy >5

Yes? So you need the logic that tells it which value to use?

=DATEDIF(A6,B27,"M")*(C115*8). This is the formula i am using to calculate vacation accural. it also adds what has accured from the beginning of the year to current. A6 is date of hire, b27 is current pay period, C115 is the accural rate. then it put the amount in a different cell in that cell if they have been here less than 5 years it 10 hours a month more than 5 years it should say 12 hours a month. But mine is adding another 4 hours when it comes to the anniversary date of 5 years. Does that make any sense now? thanks

I don't have the DATEDIF function installed on my computer, so I can't check to see what it does. I'll assume it counts the number of months (M) from the start date (A6) to the current pay period (B27).

If that's true, then your formula will take the total number of months in service, [b]including the current month even though the pay period is in the future[/b], then multiply that by the value represented next, which is 1.5 x 8 which is 12. That formula does not take into account the fact they would have only earned 10 hours a month prior to achieving 5 years of service, yielding 732 hours based on what you've told us. It assumes all months earned vacation at the same rate.

Also, where in your spreadsheet do you take into account those with less than 5 years of service so you can change the accrual rate (which is currently 1.5 x 8 = 12) to something which represents only 10? Is that in C115? Is the value in C115 calculated elsewhere and stored in C115, or does C115 contain the actual formula to calculate how many months of service they have and set the value to either 1.25 or 1.5?

Where is the current total number of vacation hours earned stored? Where is the total amount of vacation used stored?

Have you looked at the formula which determines WHEN to set their accrual rate up to 12? Does it boost that number up IN the month they have their 5th year anniversary, or does it boost it to 12 in the month following their 5th year anniversary? Does it take leap years into account?

You need to give us the whole picture or we'll never be able to track down the error.