Help need Formula to calculate vacation days left based on anniversary date.
My employer is changing from anniversary date to calendar year effective January 2016. I am having problems getting the formula to come out correctly. I want to use there anniversary date in 2014 to 2015 when it starts over. and then as of July 1 2015 to see how much they would have for the year until Dec 2015. also need to add days already use between the 2014 and 2015 time frame
example
my anniversary turns over 3/15/15
hire date 3/3/2014
0-2 years 10days
3-14years 15 days
15 or more 20 days
Last edited by yreynolds; 06-24-2015 at 03:02 PM.
That can be a complicated calculation. Why does your anniversary turn over on 3/15 if your hire date is 3/3? What happens if your 15th anniversary is in, say, September of this year? How is the proration of the 20 days done, in that case? Some examples of what you expect would help.
Bernie Deitrick
Excel MVP 2000-2010
ok, I didn't make myself clear. the vacation is on anniversary date currently so if my hire date is 3/3/14, I accrue again on 3/3/15. we are using the cut off of July 1
also, the old policy you only get 1 week vacation 1-3 years, 3 or more 2 weeks
Use the little known DATEDIF function. Assuming A1 contains anniversary date
=DATEDIF(A1, TODAY(), "Y") gives number of years you've worked there.
Maybe put it inside a LOOKUP
=LOOKUP(DATEDIF(A1, TODAY(), "Y"), {0,3,15}, {10, 15, 20})
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I have attached a sample data spreadsheet
ok, that works on pulling the years of service put effective July 1 the new vacation policy goes into effect.
1-3 years 1 week vac
3-15 2 weeks vac
I am trying to give them what they currently have figure the last six months of the year. Effective Jan 2016 we go to calendar year.
You made everything nice and neat since everyone started working on January 1st. What if you started working on May 1, 2012? As of Jan 1 of this year, you would not have yet worked for 3 years so you would accumulate at a rate of 0.01 until May 1, then you would accumulate at 0.03 per day?
How would I calculate vacation time that would be earned from July -Dec 2015? I have what they would accrue from the anniversary date until current. I just need a figure a way to calculate the july -dec and add them together.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks