• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentage Formulas

nishi23

New Member
I am new to this community and need help creating this spreadsheet. Our employees accrue time based on percentages, and this spreadsheet would be great to represent their accruals for time tracking and vacation. This workbook was created by an unknown person and is based on hours, but we need help making this work for our percentage based system. We want to track our employees' sick time, but they do not get penalized for them. We like the technology that this excel workbook offers to track their vacation accruals, but we are having trouble converting it for percentage representation. Attached is a sample worksheet and a table showing the vacation/accrual rates and parameters. Please help!!
 

Attachments

  • NEW FY2013-14 Template.xls
    82.5 KB · Views: 7
  • Percent Calculation for Accrued Time.xlsx
    11.9 KB · Views: 6
Hello @nishi23,
Welcome to the forum.

I have a few questions.
If someone works 100% in January and February, 50% in March and April, and 10% in May to December, how should their vacation be calculated? Is that based on the table in the second doc, and the amounts are to be added based on the percentage worked in each month?

In the second doc, what are the time units in the Monthly, Yearly and Max columns? Is that in hours, or something else? If in hours, will the calculation for determining # of vacation days be determined based on an 8 hour day?

What is the significance of the shaded cells?

What is the significance of the three tabs in the first doc?

Please provide any additional information that would be helpful.

Cheers,
Sajan.
 
Hi Nishi,
Please let me know what excatly you want to achieve... give some examples from the attached files..

Regards,
Ravi Verma
 
Hello @nishi23,
Welcome to the forum.

I have a few questions.
If someone works 100% in January and February, 50% in March and April, and 10% in May to December, how should their vacation be calculated? Is that based on the table in the second doc, and the amounts are to be added based on the percentage worked in each month?

In the second doc, what are the time units in the Monthly, Yearly and Max columns? Is that in hours, or something else? If in hours, will the calculation for determining # of vacation days be determined based on an 8 hour day?

What is the significance of the shaded cells?

What is the significance of the three tabs in the first doc?

Please provide any additional information that would be helpful.

Cheers,
Sajan.


If someone changes percentages in a given period their vacation accrual with fluctuate as well as represented on the chart. So if they worked two months at 100% they accrue 3.4 vacation days, and then they work the next two months at 40% then they accrue 1.36 days for those two months with a total of 4.76 days accrued through the end of the month.

The time units in the chart represent the amount of time accrued each month, per year (12 months), and the max amount of days that they can carry forward. If they have accrued 37 days vacation at 100% time, they may only carry forward 35 days to the following fiscal year. Our fiscal year runs from July 1 – June 30. The accruals are not in hours because our employees are paid based on percentage of time worked.

The shaded cells in the Sick box represent that they do not lose sick time. This is a template used in another department that is based on hourly wages and gain sick and vacation time based on hours worked, where as our employees do not get dinged for sick days, but accrue vacation monthly and work based on percent. 100% time typically means Mon-Fri, but that is not the case in all situations hence, why we cannot calculate for hours.

The shaded boxes on the main tab represent 2013-2014 Saturday/Sundays.

The three tabs that state 1.0 Day/ 1.5 Day/ 2.0 Day is the accrual vacation days for hourly employees. Our values are not rounded up since we have quite a few PT employees. So ideally, for us, they will be labeled 100%, 80%, and so on with the appropriate templates that I can use for new employees.

The main purpose of this chart is that it is a working and functioning spreadsheet that reflects their vacation accrual that is up to date, and less hassle than what we are working with right now. Our current system is updated monthly, and we are calculating the accruals as they come in. Just going forward since we are expanding this template would suit our needs.

Please let me know if you have further questions.
 
Hi Nishi,
Please let me know what excatly you want to achieve... give some examples from the attached files..

Regards,
Ravi Verma



Hey Ravi,

We are trying to use formulas to create a spreadsheet that keeps track of time off allowed for employees who work different percentage amounts of time. Let's use a hypothetical example.

From the previous fiscal year I carried over 3 days of vacation.
I work 100% time and accrue 1.7 month, and have accrued 6.8 days vacation this fiscal year (starting July 1, 2013). I will be taking off 5th-8th in November. How much available vacation time will I have at the end of November?
The max number of days that I can carry forward are 35 at 100% time, and I accrue 20 days per fiscal year.
100% X (1.7(12)) <= 35
3 + (1.7 X 5) – 4 days = 7.5 available vacation days at the end of November

Hope this clears things up!
 
Hello Nishi,
Attached workbook contains two worksheets named "TimeOff Sheet" and "Reference"

I created the "Timeoff Sheet" based on what I understood as your requirements:
  1. You are looking to track employee time-off per fiscal year
  2. Employees may carry over vacation days from one year to another, subject to some max amounts
  3. The amount of vacation allowed to be carried over from one year to another is based on the percentage of time worked by that employee in a year, calculated on a per month basis
  4. Only vacation days are calculated for carry over and use
  5. However, other time off days may be marked on the worksheet (such as sick days, jury duty days, etc.)
  6. If an employee stops working before the end of the fiscal year, their vacation benefits are frozen as of the last month worked
  7. You wish to know the total number of vacation and sick days used as of a certain date.
  8. You wish to know the total number of vacation days lost at end of year (due to constraints on vacation amounts that may be carried over into the next year), if employee does not take them prior to end of year


Since there were gaps in your explanation, I made up a few rules:
  1. Days carried over from prior year can be used by end of current fiscal year
  2. Employees take time off in whole days only
  3. Vacation accrual is calculated per month. Since the table you supplied had discrepancies in monthly amounts versus yearly amounts, I assumed the discrepancies were due to data entry errors on your part, and not as part of an effort to short-change your employees! (For example, if an employee worked 100% for 12 months, the yearly amount should be 1.7*12=20.4 and not 20 as you had in your table.)
If these rules are not appropriate for your organization, feel free to modify the worksheet to suit your needs

"Reference" worksheet
  • In the "Reference" worksheet, setup the reference data, such as list of holidays, fiscal year start date, time-off codes, etc.

"Timeoff Sheet" worksheet
  • The "Timeoff Sheet" displays any company holidays you have setup on the "Reference" sheet. (These are calculated using formulas. However, these formulas get overwritten by any time-off codes you select. To recreate the display of the holidays, you will need to remove the "data validation" for a given cell, apply the formula, and then reapply the data validation restriction to TimeOffCode list.)
  • The sheet displays Saturdays and Sundays with a grey shading. The worksheet does not perform any validations on whether a user entered the time-off code on a working day, weekend day, holiday, etc. If your users cannot be trusted to enter time-off codes on working days only, your options include adding some data validations to check that the time-off codes are applied to valid days.
  • The sheet calculates the amounts of vacation earned / accrued based on the "% worked" you indicate for a given month. (If the % worked is empty for a given month, it is assumed to be zero, indicating that the employee did not work that month.)
  • As you record various time-off events, the calculations reflect the number of vacation days remaining.
  • If an employee takes more vacation days than available, the sheet displays the vacation amounts in red
  • Any "white" (i.e. unshaded) cell is for user input

I would encourage you to test the worksheet thoroughly to ensure it is working as you expect.

Cheers,
Sajan.
 

Attachments

  • Chandoo-Nishi-Time Off calculation.xlsx
    60.6 KB · Views: 7
Back
Top