Day Accrual Formula
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
ADVERTISEMENT
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
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
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
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 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
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
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
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
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
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
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
Oct 7, 2009
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View 6 Replies
View Related
Jul 30, 2014
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
View 2 Replies
View Related
Dec 22, 2009
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
View 9 Replies
View Related
Mar 27, 2014
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
View 2 Replies
View Related
Aug 24, 2014
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx‎
View 3 Replies
View Related
Apr 23, 2009
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.
I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6
View 3 Replies
View Related
Jul 22, 2014
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
View 4 Replies
View Related
Jul 16, 2007
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
View 10 Replies
View Related
Apr 9, 2009
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View 14 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Jan 31, 2008
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.
View 9 Replies
View Related
Jun 1, 2006
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies
View Related
Feb 23, 2008
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View 9 Replies
View Related
Dec 19, 2012
I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.
=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.
My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)
This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")
View 8 Replies
View Related
Apr 3, 2014
In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.
What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.
In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).
In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).
So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.
How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx
View 2 Replies
View Related
