Formula Fix (today)-(month/day Of Hire) For Vacation Accrual
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
ADVERTISEMENT
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
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
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 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
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
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
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
Nov 13, 2008
I've inserted the current date into an excel form using the TODAY() function.
I also need to provide the form in French, and when I change the language for the cell with the TODAY() function, it automatically abbreviates the month. For example, today's date appears as "13 nov. 2008" rather than "13 novembre 2008" as I would like it to appear.
Any ideas on how to change this?
View 8 Replies
View Related
Apr 4, 2008
I work for a small company and we are attempting to change the way that employee's vacations are calculated. My Excel knowledge is just enough to get me by. I am building a simple spreadsheet for each employee. We want to make their vacation time roll over each year on their anniversary date. In prior years, we would just use January 1 after the employee had been there a year or two. My question is can Excel take the employees start date and add the additional hours that are earned on the anniversary date automatically.
View 9 Replies
View Related
Nov 19, 2009
If today = any date in November, how can I display in a seperate cell that that is month 8 of an April - Mar Financial year.
A4 =Today() 18/11/09
a5 = can this display as 8 and then 9 when a4 becomes a december date?
View 3 Replies
View Related
Oct 17, 2008
I am trying to project the next 12 month-end dates, based on today's date. I can do that using the EOMONTH function ... see exhibit below ... present month, 1 month out, 2 months out, last month. However, this workbook must be sent to many people and many of those folks will not have EOMONTH functionality because that requires the Analysis Toolpak functions to be added in. How can I accomplish this using standard Excel functions?
Present Month >>> =DATE(YEAR(NOW()),MONTH(NOW()),1)
One Month Out >>> =DATE(YEAR(EOMONTH(NOW(),1)),MONTH(EOMONTH(NOW(),1)),1)
Two Months Out >>> =DATE(YEAR(EOMONTH(NOW(),2)),MONTH(EOMONTH(NOW(),2)),1)
Eleven Months Out >>> =DATE(YEAR(EOMONTH(NOW(),11)),MONTH(EOMONTH(NOW(),11)),1)
View 8 Replies
View Related
Aug 22, 2008
I have 22-08-08 in Cell A2 I would like it to change to 22-09-08 on 23-08-08 ideally using edate (but not neccessarily).
Perhaps I can add a formula with conditional formatting eg formula is = "On Today()+1 Add 1 month to cell A2"
View 9 Replies
View Related
Jan 23, 2009
I have a formula in a cell that counts down the days of the month each day using the today function...that part works perfect...But I have some conditional formatting that highlights the row when their is only 5 days left. Basically the row stays highlighted yellow for anything >=0 ="0",$BJ3
View 9 Replies
View Related
Sep 28, 2009
=Date(year(today()),month(today()),A1) works great, However I need the formula to subtract 1 month if the value returned would be greater than today's date.
View 2 Replies
View Related
Jul 9, 2013
I have a spreadsheet and every row has an incident date. What I would like to do is calculate which relative month it is compared with today's date. I know how to show if it is in the current month, but what I want is something like the following:
Today's date is 9 July 13
Row 1 date = 9 June 13, hence calculation = -1
Row 2 date = 17 May 13, hence calculation = -2
Row 3 date = 29 Jan 13, hence calculation = -6
etc
Some sort of month to month comparison resulting in an integer.
View 2 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Mar 11, 2014
I need to devise a formula by which Excel will automatically award employees with service-incentive leave on their anniversary date, starting TWO YEARS after they were hired.
For example, if John Doe was hired on 02/20/1998, he earns 1 day leave beginning 02/20/2000 and on each hire anniversary thereafter. How do I make Excel do that?
View 6 Replies
View Related
Aug 22, 2009
I have a business and i run payroll for my employees twice a month (semimonthly).
Date of paycheck will be 16th and 31st.
So if employee was hired on say 7/5/2009 then this employee will have 3 paychecks as of today
(1st paycheck from 7/1/2009 to 7/15/2009
2nd Paycheck from 7/16/2009 to 7/31/2009
3rd Paycheck from 8/1/2009 to 8/15/2009)
i need to know the # of pay checks for each employee computed in Cell C3 to C7.
View 6 Replies
View Related
Aug 9, 2013
I work for a equipment hire company, where various computers and structure are hired out on different jobs. We are getting to a point where keeping track of stock is a problem, we need to quickly be able to check if an item is already booked on an upcoming job, and keep track of home many are in stock. ETC.
View 1 Replies
View Related
Feb 13, 2010
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies
View Related
Mar 20, 2009
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
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
Mar 5, 2007
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
View 9 Replies
View Related
Jan 7, 2014
I have dates in column A a6:a5000. I need a formula to add a remark "Today" in column B for the current date. In short the remark in column B should display the remark "Today" for the current date daily. The dates in column A are in 'DD MM YYYY'.
View 2 Replies
View Related
