# Excel 2007 : [SOLVED] Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked

1. ## [SOLVED] Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked

Hello,

I finally figured out the formula to calculate vacation time based on anniversary date for full-time employees.

Now, within the same sheet, I need to calculate vacation time based on anniversary date AND hours worked for part-time employees.

Here is the criteria:

0-3 years of service 1 hour for each 20 hours worked with a maximum of 4 hours
3-15 years of service 1 hour for each 13 hours worked with a maximum of 6 hours
15+ years of service 1 hour for each 20 hours worked with a maximum of 8 hours

Example: A part-time employee works 50 hours per pay period and his start date was 8/25/2002. Manually calculating, he would earn 3.8 biweekly
A part-time employee works 32 hours per pay period and his start date was 6/19/2011. He would earn 1.6 biweekly

Thanks!

2. ## Re: Excel Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked

Hello pinksylk, welcome to excelforum

If you have hire date in A2 and hours per pay period in B2 try this formula in C2 for bi-weekly hours

=MIN(B2/LOOKUP(DATEDIF(A2,TODAY(),"y"),{0,3,15;20,13,20}),LOOKUP(DATEDIF(A2,TODAY(),"y"),{0,3,15;4,6,8}))

format C2 as number

Note: the years in service is based on todays date, is that right? No rounding is used you might want to enclose in a ROUND function to round to 1 decimal place or similar

3. ## Re: Excel Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked

YES, DLL!! This worked! OMG, thank you so much!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1