Vb Or Function: Reset Vacation On Anniversary Date
Nov 6, 2009
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well.
I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down
my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern.
a. need to reset vacation hours for the year based on annual anniversary date
-"vacation hours per year"
-"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8)
this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec
1year=40 hours
after 1 year anniversary =80 hours
3 years+ =120 hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)
second part of my project:
b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate)
ATTACHED TEMPLATE
things i am changing in design:
vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours)
-thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking
anniversary date - 3 months in a statement to reset it back to 0 after the three month period.
I need to take the month and day of a seniority date and add it to the reset year but am having difficulty doing so. If the seniority date if July 4, 2008, I need to convert it to July 4, 2012 for my calculation to work. My formula is this:
Our Company has long term leases that increase according to the Consumer Price Index every three years.
I can easily calculate the trigger date down Col. A. What "If" formula could I use in Col. B to recognize the triannal date and rate (or forecast) the new rent?
I've thought of using an array and Match but I really don't know how to write the formula.
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?
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
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
On a worksheet the user can elect to view monthly data. The "From" and "To" months can be any they choose. For example, if they choose "JAN" and "JUN" as the "From" and "To" months, "JUL" through "DEC" are hidden from view.
I would like to be able to show "Year to Date" totals based on the "To" month. I refer to the "To" month as the "ActiveMonth".
This procedure captures the "To" month (the "ActiveMonth") based on their input:
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
I have an excel file with several worksheets and 1000's of lines of data.
Protection is already given courtesy of having to enable macros or sheets are hidden, and copying of data is not possible. It is however unfortunately possible to copy the excel file, therefore I would like to have code to to render the file inoperative if a date elapses. In addition since the file is password protected (as regards alterations - read only for others), I would like the clock to increment for example 7 days further every time the correct password is entered. In other words if the time period elapses simply entering the password will reset the clock and permit all users access for another week once the file is saved..
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
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.
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....
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.
I want to setup a spreadsheet for the year (started mid July) to understand how much holidays I accrue, so I work 37.5 hr pw, I accrue 4.5pw - holidays taken, what is the formula?
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.
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): ....
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.
I am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover.
I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be.
Breakdowns are as follows: 1-2 years - 1 week vacation - 40 hours eligible for carryover each year 3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year 5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year 10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
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.
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:
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.
I have a vacation data of my company. I want to calculate difference between two dates (relieving date & rejoining date) after excluding holiday(which I will decide as per my company policy) also note that now i m in gulf country hence here Friday is weekly off not Sunday.
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
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.
I have a payroll spreadsheet and I want to highlight cells that have hours entered for vacation. For example, in cell E4 I put the job name which is "VACATION" and in cells G4 through M4 I put the hours in for each day. (G4 is Monday, H4 is Tuesday, etc.)
I tried to put a conditional formatting using the following formula ="IF(E4=""VACATION"")" then I chose the fill color however it is not working.
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
