+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    02-24-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Exclamation [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!
    Last edited by pinksylk; 02-24-2012 at 12:38 PM. Reason: To mark as solved.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Audere est facere

  3. #3
    Registered User
    Join Date
    02-24-2012
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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