# Software

## Question

Locked

### Excel help

By ·
I have a timesheet created in Excel. Most of it is working correctly however it is not calucating vacation time correctly. I have three cells one= years of service one is the accural rate and the third cell is where it is supposed to calcuate the vacation based on the accural rate. When the employee reaches 5 years he gets three more days vacation. In the spreadsheet it is giving the employee 4 hours that he does not get. Any help would be greatly appreciated. I have a copy of the timesheet if you need to see it. Thanks

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)
| Thread display: Collapse - | Expand +

Collapse -

### Don't quite understand

by In reply to Excel help

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?

Collapse -

### confirm equation

by In reply to Excel help

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?

Collapse -

### Explanation

by In reply to Excel help

=DATEDIF(A6,B27,"M")*(C115*. 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

Collapse -

### Can you give us an example of actual data?

How about telling us what you've got in:

A6
B27
C115

Is the current pay period in the future?

Collapse -

by In reply to Can you give us an exampl ...

Sorry about the delay in response.In A6 is the hire date 4/15/2004. B27 would be the end of the payperiod which this one is june 7 2009. c115 is the vacaation accural rate in this case it is 1.50.

Collapse -

### Still some pieces of the puzzle missing......

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, including the current month even though the pay period is in the future, 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.

Collapse -

by In reply to Still some pieces of the ...

It was assuming all months are the same accural which is where I am having a problem. I need it to adjust from their anniversary paydate forward and leave the other months alone.

At the botton of the timesheet I have three boxes. One for years of service( years of service takes current date from a cell that has the hire date in it), one for sick accural which does not change and one for vacation accural. the vacation box has a formula in it that takes the number in the years of service cell and puts the accural rate in the vacation cell.
Its supposed to changing in the month of their anniversary.
Would it be helpful if I emailed you a copy of the timesheet?

Thanks for all the help so far.

8 total posts (Page 1 of 1)

## Related Discussions

• 3

#### Searching for a professional Apple Music Converter

matthewwalker531 //

• 1

#### red x on internet icon, win 7

gretsky2@hotmail.com //

• 0

dwolovi //

• 0

#### Do you recommend CMar4Pabx call accounting software

Marwan4Software //

• 5