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.

View 7 Replies


ADVERTISEMENT

Seniority Dates - Vacation Reset Year

Jul 25, 2012

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:

=IF(TODAY()>(E3+365),(H3-(SUMIF($J$2:$BK$2,"Taken",J3:BK3))),"")

Which works fine for all seniority dates in 2011. These are my column headings:
E
F
G
H
I
J

L

PPE
2011
5/5/2012
5/12/2012

Hire Date
# of Days
Balance
Cur Yr Accr
Accr
Accrued
Taken
Accrued
Taken

View 1 Replies View Related

Recurring Anniversary Date

May 4, 2009

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.

View 7 Replies View Related

Hire Date Anniversary And Leave Updating?

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

Counting Number Of Days Between Anniversary Day And Payment Due Date

Aug 11, 2013

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.

View 6 Replies View Related

Stop Vacation Hours Calculation On Vacation Day

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

Reset Range In Function

Aug 1, 2009

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:

View 7 Replies View Related

Count Function :: Vacation, Personal Time, & Sick Time

Nov 17, 2005

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.

View 13 Replies View Related

Date Expire And Password Reset

Apr 30, 2007

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..

View 5 Replies View Related

Macro To Transfer Data From One Sheet To Another Based On Date And Reset Fields

Jul 16, 2014

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.

View 1 Replies View Related

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)

Dec 8, 2009

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?

View 6 Replies View Related

Vacation Planning

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

Vacation Lump

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

Vacation Accrual

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

Vacation Accrual

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

Vacation Days Calculator

Dec 25, 2012

I am looking for a good and simple formula to calculate vacation days.

View 2 Replies View Related

Accrue Vacation Entitlement

Sep 18, 2012

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?

View 8 Replies View Related

Yearly Vacation Formula

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

Vacation Accrual Formula

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

Vacation Accrual With 3 Month Roll Over

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

Spreadsheet Show Vacation Time

Aug 6, 2008

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

View 9 Replies View Related

Employee Vacation Accrual And Tracking Template

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

Formula For Calculating (Accrual) Vacation Time

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

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 View Related

Vacation Data - Calculate Difference Between Two Dates

Mar 5, 2012

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.

View 9 Replies View Related

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 View Related

Excel 2013 :: Employee Vacation / Holiday Accrual Spreadsheet

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

If Then AND Else: Figure Out The Vacation Accrual Rate Based On Length Of Employment

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

Payroll Spreadsheet - Highlight Cells That Have Hours Entered For Vacation

Jun 18, 2014

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.

View 3 Replies View Related

Automating Out Current Loan Period From Start Date And Today Date In IMPT Function?

Jun 12, 2014

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.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved