Time Accruals!

**adkmom** · 07-25-2012, 12:57 PM

Hi Everyone!
I am working from scratch on an excel sheet for my employer to track employee time. We also want to use this worksheet to print every month for the employee to sign off that they agree on their time (per their union contract). That being said, I have set up every employee with their own worksheet showing a calander breaking down vacation, sick, personal and holiday time used that month with a summary on the bottom for their calcuations (beginning balances, earned, subtotals, used time and balance remaining). Sick, Personal and Holiday I have no problem with as they are nice even hours. I am having a problem with Vacation. Our accruals work as the following...

Year 1 = 3hr, 20 min per month
2+ = 6 hrs, 40 min per month
5+ = 10 hr per month
10+ = 13hr, 20 min per month
20++= 16 hr, 40 min per month

they also can not exceed 160 hrs of accrued vacation leave.

We have a very small amount of employees, so currently earned is a pre-entered # and not tied to their anniversary dates....and can stay that way.

Basically how do I tell it to round the hour up at 60 and not 100 ( to accommodate for the minutes )?

I have attached an example.
Any help is very much appreciated!!!!

THANK YOU!!

**ChuckYeager** · 07-25-2012, 02:16 PM

Hi adkmom

More information on the computation and some notes in the spreadsheet as to where do you need help with the time rounding off would be helpful, in figuring this out.

Regards

Chuck.

**adkmom** · 08-01-2012, 10:09 AM

The area I need help is in the vacation accurals. If an employee accrues for example 6hr and 40 min per month over a two month period with no time used a straight calculation will come up with 12.80 when adding. The correct calcuation should read 13.10
(as in 13 hrs and 10 minutes). I continued down the spreadsheet to see if it would eventually "catch up" with itself but the employee looses time. I need to tell my calcuations in the accrual summary boxes that it needs to round up at 60. Does this make sense?

**ChuckYeager** · 08-03-2012, 06:04 AM

Hi Adkmom,

I have worked out a new sheets for you in the same file called "New Sheet" & "New Sheet 2013"
There is a time value correction formula which has been explained in "Sheet1", however since the values will be computed in some crazy fractions I have added rows below each calculated value to show the accruals and consumption in "0h00m" format.

In "New Sheet", Which is to be used for the 1st year, i.e. 2012, when you begin the using the sheet, has no impact on when did the employee join the company
The summation formulas for February and the latter months are different from the summation formulas for January, as I have assumed that you will be entering the brought forward values for January from another sheet in "h.mm" decimal fractions.

In "New Sheet2", Which is to be used for the 2nd year and subsequent years
All the summation formulas for all months are the same, as I have assumed that you will be using the carry forward value from "New Sheet" as brought forward values in "New Sheet 2013"

I have also added the 160 hour limiter for limiting hours to 160 hrs maximum to be carried forward at the end of the year.
The only complicated formula in the sheet as per me is the formula for Used summation. It basically is an ArrayFormula for sum what it does is add the hours entered in the month above and add the fraction difference for correction for each entry.

If you have any questions I will be glad to answer them.

EXAMPLE.xls

Regards

Chuck