I want to find out the seniority of the employee to determine how much time off they are entitled to.
New hires get partial first year and then after 1 year 2 week, after 6 years, 3 weeks and after 210 years 4 weeks.
New hires are a portion of 2 weeks, based on when in the calendar year that they have been hired.
I have looked thru the threads and cannot seam to find this one.
Any help is appreciates.
Cynthia Pepperman
I think we need a bit more information than that, like where are the hire dates/start date, and do you want the answer in days? It would help if you attached a sample workbook (the FAQ describes how to). Also, is that meant to be 20 or 21 years to be eligible for 4 wks leave?
Pete
Need more information. By Seniority, you mean how many years they've been employed?
How do you determine Partials in the first year?
Maybe something like (with start date in F4)
=INT(IF(DATEDIF(F4, TODAY(), "y")<1, DATEDIF(F4,TODAY(),"m")/12*10, IF(DATEDIF(F4, TODAY(), "y")>=10, 20, 10)))
which shows vacation time in days
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
Yes I mean years.
Attached is a short file.
The hire date is 8/31/2013. Today is 1/14/2015. Their seniority is 1 year 4 months and 14 days.
For a partial year, anyone hired after January 1 2015 will get a portion of 2 weeks. I.E. hire date 4/1/15 would be 9/12 therefore vacation is 9/12 of 10 days = 7.5 days.
Sorry, I type too fast 10 years equals 4 weeks.
I think this answers all questions.
Cyndi
To actually spell out how many yrs, months, days they've been with the company, I used this formula
=DATEDIF(B4,$C$2,"y")&" y " & DATEDIF(B4,$C$2,"ym")& " m " &DATEDIF(B4,$C$2,"md") & " d"
To calculate vacation days, I used this formula (I assumed everything is rounded down)
=INT(IF(DATEDIF(B4, $C$2, "y")<1, DATEDIF(B4,$C$2,"m")/12*10, IF(DATEDIF(B4, $C$2, "y")>=10, 20, 10)))
To learn more about the DateDif function, this is a good reference
http://chandoo.org/wp/2011/05/16/lost-excel-functions/
Thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks