Vacation Accrual Based On Completion Of 90 Day Probation?
Nov 29, 2013
I need to create a tracking sheet for my vacation. The vacation starts to accrue here after a 90 day probation following the hire date. Each individual is allowed 40 hrs/year. I also need to see the hours accrued, the hours used & the balance. Everyone's balance renews on their anniversary. I will fill in the blanks for the names in one column & the hire date in the next column and I would like formulas for the following columns.
View 12 Replies
ADVERTISEMENT
Mar 26, 2009
I would like to know how to figure out the vacation accrual rate based on length of employment.
Lets say for years 0-5 the accrual rate is 3 hours per pay period.
for years 6-10 the rate is 5 hours per pay period
for years 11-15 the rate is 7 hours per pay period
for years 16-20 the rate is 9 hours per pay period
and for years after20 the rate is 10 hours per pay period.
I want a formula that will look at say cell C5 (which is the cell that includes length of service) and output the accrual rate. Example. The number 12 is in cell C5. I would like the cell with the formula (D5) to say the number 7 (which is how many hours accrued per pay period based on the table above.
View 3 Replies
View Related
Jan 11, 2008
have tried to modify it for my application but have been unable to… In my situation all of the employees earn vacation time as follows:
After 1 year=5 days
After 2 years=10 days and continues until
After 5 years=15 days
There is no level increase after the 5 years.
There is no need for a distinction between H=(Hourly) and S=(Salaried) employees so I am not sure Column F is necessary.
As you can see, it seems to work for the first two years (see rows 4 & 5). I cannot get it to work after that.
I would also need a termination date Column to stop the vacation accrual.
Additionally, because there are several hundred employees, I would like to be able to copy the formulas down Columns H & I and have the columns left blank until an entry is made in Columns E, F or G....
View 9 Replies
View Related
Apr 22, 2008
After 6 months from date of hire, employees earn 1 week.
On the 7 month anniversary date of employment, employees begin to accrue time off – 1.25 days a month
15 days total for a year (for employees under 5 year service; 20 days for employees over 5 year)
Only 2 weeks allowed per calendar year to carry-over into new year.
So the problem I'm having is that on the day of the aniversary of 180days or employment I can grant 5 days of vacation but the day after it is back to zero. ( i know i should use 6 months but ican't figure that out either)
I need the employee to have that 5 days to start as soon as their 6 month aniversary hits. I need some sort of "IF" statement that will only GRANT the 5 days IF the 6 months is within this current time frame.
Basically becuase this formula is key'd off the HIRE DATE everyone has a hire date some are 4 months old, some are 2 years old some are 10 years old. I just want someway to show them as New Hire that is under six months then when the hit 6 months GRANT 5 days of vacation then the day after they still have the 5 days and they start accruing vacation time like everyone else.
View 9 Replies
View Related
Dec 6, 2006
I need to write a formula that will automatically accrue employee's vacation time depending on their date of hire. Here is the policy:
During first calendar year vacation will accrue at 5/12 days (3.34 hr) per complete month of service. Beginning January 1st following date of hire two weeks each year. Beginning January 1st of tenth calendar year of service three weeks each year.
Here is the formula that I have came up with already (Hire Date is in cell B2): ....
View 9 Replies
View Related
Apr 26, 2014
I'm trying to make a vacation accrual sheet, and I think I have the basics of it down, but I'm running into a problem figuring out how to put in roll over days from the previous year.
Every year on January 1rst, accrual resets. Any days that haven't been used are eligible to be used for 3 months after January 1rst, but after that (on April 1rst), the old days are gone, and the employee is back to using the year's current accrual days. I'm not sure how to do that.
View 11 Replies
View Related
Mar 27, 2014
I'm looking for a vacation spreadsheet that tracks vacation has a column that lists the carryover and the yearly accrual. Ive seen several that list all employees but I am looking for one that is by employee. Where you can list the dates and it totals the days and links back to current available and deducts the days. I saw one online for purchase I like but was not able to download.
View 2 Replies
View Related
May 22, 2012
creating a formula to accrue PTO as well as track available PTO as it's used. And, not go negative if possible... if an employee reaches 0 PTO, they just take time off without pay, so a negative balance would cause issues with the calculations. Here are the specifics:
Pay periods are from the 1st of the month through the 15th. and the 16th through the end of the month. Each employee receives 3.33 hours of PTO for each pay period. Employees are eligible to START accruing PTO after 6 months of employment.
[URL]
That seemed close, however I'm just not efficient enough in Excel to understand exactly what the formulas are doing... specifically the formula for available vacation. I think I have the Rate formula figured out by using:
=IF((TODAY()-C2)>160,3.33,IF((TODAY()-C2)<160,0))
View 7 Replies
View Related
Feb 9, 2010
I'm trying to create a formula in Col D that will give me the difference of (Todays) Month/Date and (Date of Hire). The shaded rows at the end do not provide a correct answer.
How do I make the Column formula eliminate the 1 (due to hire mnth/day falling between 1/1 and current mnth/day). I've put about 30 hours in this formula, tried it many different ways. . . and realize I just can't get it.
I'm all for redoing the whole spreadsheet if you have a better way of doing vacation accruals
View 6 Replies
View Related
Jul 22, 2014
I used Excel 2013 template to create employee absence schedule but I want to add if they have enough accrued hrs/days.
Currently, I need formula so their holiday balance is accruing every week.
On a weekly basis employee is accruing 3.85 or 4 hrs (depends how long he worked for the company).
Less than 5 years rate is 3.85 hrs and more than 5 years is 4 hrs.
I attached example sheet for accruing vacation.
E column is employee current balance from last week.
View 6 Replies
View Related
Jun 18, 2009
Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.
View 6 Replies
View Related
Nov 11, 2009
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2)
basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips
View 3 Replies
View Related
Feb 5, 2007
I have got Names in column A and Join_Date in column B. In column C I want those whose Probation period is going to finish this week. Probation is of 90 days.
View 9 Replies
View Related
Mar 6, 2007
I need to make a formula that will accrue vacation days automatically.
We accrue days on the 15th of the month( If you start on February 15th, you have accrued the days for February).
If the person has been here for 3 years they accrue 15 days a year.
If they've been here for 7 years, they accrue 23 days.
If they've been here for 10 years they accrue 30 days a year.
All days are accrued evenly over the 12 months.
For example If someone started 3 years ago on March 3rd, starting March of 2007, they will be accruing 23/12 days a month, but for January and February, they will accrue 15/12 days a month.
The formula must work off their start date.
View 9 Replies
View Related
Mar 1, 2013
General accrual spreadsheets. I'm looking for an accrual spreadsheet where I can estimate freight and also for our gas, electric and water usage which is tracking usage down to the day.
View 2 Replies
View Related
Apr 13, 2014
i ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
View 1 Replies
View Related
Nov 24, 2008
The macro is designed to grab a closing stock price for a date specified for a list of Symbols in column A and the date in column B. The closing price is then displayed in column D.
The problems (there are two that I found) I am running into is that when the Macro encounters a symbol in Column A and is not recognized, an error message pops up and the macro stops altogether. I want the macro to continue to run down the list but I can't figure out where in the macro it is stopping and, more importantly, how to make it continue. I have attached a copy of file if needed (if opened, you can see it stops working after row 11).
View 5 Replies
View Related
Feb 20, 2009
I have 2 command buttons on a sheet, one of which I would like to upgrade to delete the other, as well as itself, upon completing the rest of its functions. That's right, normal VBA isn't fun enough anymore, I want kamikaze suicidal code that wipes itself off the face of the earth.
My colleagues are... less than comfortable with VBA. I use it to produce some of the forms which I send to them, and if I could delete the code automatically after running, I'd save myself some time and/or save them some confusion.
View 2 Replies
View Related
Dec 31, 2009
I'm getting a "Subscript out of Range" error on a routine that was running fine a few days ago. It's part of a larger macro, so I've pasted the full code for that macro below. The problem is in the routine labled "Replace Regional Labels". What that's supposed to do (and DOES do) is to replace labels such as 110 or 11000-CSD with 11000. The error message comes up AFTER the routine has completed it's work. The offending line is:
View 2 Replies
View Related
Jun 4, 2009
I need a simply excel formula or macro which calculates the numbers of months i need to accrue for certain costs. The data I have includes the start month of the invoice, the end month of the invoice (i.e. showing how long the invoice is relevant for, quite often 1 year), and obviously the current month.
(eg. a 12 month invoice with a start date of Oct 08, end date Sep 09, current May 09 = 8 mths to accrue)
(eg2. a 12 month invoice with a start date of Apr 09, end date of Mar 10, current May 09 = 2 mths to accrue)
(eg3. a 12 month invoice with a start date of Jun 09, end date of May 10, current May 09 = 0 mths to accrue)
View 3 Replies
View Related
May 8, 2014
I need to list multiple items that end in, example:at time entered there is 45:14:50 = Hr:Mn:Sec to completion, and would like the date and time of completion or just a countdown to 0 would work. I will have 60 to 70 items running at the same time with different start times and lengths. Picture queueing up items at different times to complete in different length of time and I need to be ready when each item is done. I know this is an Excel forum but I also use Apple Numbers so if either one works better I'm fine with that.
In the attached file, column R is the start time that I enter that needs to be calculated to column S. When I enter an item that takes 54hrs 23min 45seconds to complete, what date and time will it be ready.
View 9 Replies
View Related
Dec 26, 2012
I have the following function:
=IF(G13="",IF(H13>1,1,IF(G13>1,workdays(H13,G13,Holidays))))
The intent is to calculate the completion date against the assigned date as follows:
G13 - Typically the assign date but sometimes can be blank because someone forgets or doesn't bother to insert
For this example, let's say: 12/17/12
H13 - Completion date
For this example, let's say: 12/20/12
I'm expecting a result of '3' for 3 days
The first half of the function is intended to calculate 1 day if G13 is blank or not defined. However, when I have dates in both cells, I'm getting a "false" answer instead of a numerical value that I'm expecting.
In our workplace, we do work weekends & sometimes 7 days a week.
View 11 Replies
View Related
Jan 14, 2008
This is my first time posting to the site so I will start with a simple question. I have an email macro that will send the workbook to a specific recipient. What I want it to be able to do is close excel after the macro is ran and the email is sent. If anyone would be kind enought to supply me with a code that will work .....
View 9 Replies
View Related
Sep 28, 2008
I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.
I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.
The detail is as follows:-
View 9 Replies
View Related
Aug 28, 2007
I have a spreadsheet where I have input all of my technicians service calls and installations.
In Column C Row 5 and up to the max I assume because I'm not sure how many rows I would use in a year, I have an estimated completion date.
In Column D and the same Rows as above I will input the Actual Completion date.
What I want is if the current date is past the estimated completion date (Column C) and there is no date entered in the Actual Completion date (Column D) I would like a MsgBox to pop up saying "Please enter a new completion date on row _"
"_" would be which ever row the estimated completion date has expired without an actual completion date being entered.
View 9 Replies
View Related
Apr 8, 2006
I work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at
has around 125 employees (even w/ the constant turnover after Katrina hit).
My problem is, that my corporate office refuses to update our payroll
operations. We still use punch time cards that every other Friday I have to
manually enter time into the payroll server timesheet. Our system does not
calculate paid time off, among a lot of other things. My employees come to
me almost daily asking if I can tell them how many PTO days they have left.
The only way I have to figure it out is by going through every PTO bi-weekly
request form spreadsheet until I have added up the days taken throughout
their service year.
I am trying to build a spreadsheet in Excel 2003, that consists of:
Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date,
PTO per yr, PTO taken, PTO days left....among other personnel information.
I want to create a formula that will calculate the PTO per yr the employee
has.
After 6 months = 2 days
1 year = 10 days
2 years = 14 days
6 years = 19 days
16 years = 25 days
26 years = 30 days
(If the employee does not use the days within their service year, they loose
it.)
I also want in the PTO taken column a formula that will pull the # of days
taken from the PTO request spreadsheet that I have to send in to home office.
The employee's differ every spreadsheet, as does their placement. Once the
days taken are in their proper column, I want the PTO left to be calculated
from PTO days per year minus the days taken.
View 9 Replies
View Related
Apr 7, 2013
I have two sheets, one sheet showing the total calories burned, in the sheet two, i have the target set,
How can i change the color of cell in the target on the basis of the achivement for example the target is say 10,000 cells and after I achieve 1500, it will change the color, after 3000 it will change another color and so on and so forth.
View 1 Replies
View Related
Jul 29, 2002
I have working on a project "vacation plan" in excel.. i want a small help. i will be entering all the details of the leave for eg. 1)name 2)employee code 3)no. of days eligible for leave 4)starting day & date of the leave 5) end day and date of the leave 6) no. of today days. on sheet1.
on sheet 2. i will have a calender for the entire year. i want this calender to get updated as soon as i put in the date in sheet1 with the names of the agents in the calender & if the leaves overlap, it should change the colour with a conditional formatting. i understand it's complicated. but with the help of u champs, i am dead sure that i will make. it. as i have done it earlier on many complicated projects.
View 9 Replies
View Related
Sep 13, 2007
I'm looking to finish up a few spreadsheets regarding our vacation time off.
Our current schedule is this:
After 1 year = 40 hours
2+ Yrs = 80 Hrs
5+ Yrs = 120 Hrs
10+ Yrs = 160 Hrs
20+ Yrs = 200 Hrs
I need a formula(s) to take the hire date, determine what year they are in right now and how many hours got and on what day.
What I would like to see is (and it doesn't matter how many cells we use)...
* Which date is their anniversary date in 2007 & what year they are in.
* If their anniversary date passed already, I want to see the amount they received at that point
* If their anniversary date hasn't passed already, I want to see when they will receive (if any) and how much
View 9 Replies
View Related
Jul 29, 2014
I have a spreadsheet containing 2 worksheets. First worksheet has my Students Details and the second spreadsheet has their Campus Locations.
What I need to create is a Formula that tells me "What CAMPUS was the student LOCATED at on the Course Completion Date?"
[URL]
what I'm trying to do is as follows:
John Completes his Course on the 25th July 2013, his Student ID is 450.
I need to go to the CAMPUS LOCATION spreadsheet and first search for Johns student ID (450) in column B. Because John is listed in B2,B3 and B4 excel needs to check date in Column E to see if his completion date was before the Date he started at Campus.
If it is then the Campus he was at will be the value of Column D on that line.
If it is not then it needs to search the next line until it satisfies the condition.
View 5 Replies
View Related
