Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Calculating Accrued Vacation

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Bel Air, MD, USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,
    I'm new to Excel, so please bear with me.
    I want to calculate the accrual of vacation time, which accrues every 14 days at a rate of 4 hours per pay period (the pay period is every 14 days). I have two columns of data; column A contains pay period 1 thru 26, and col B contains the pay period ending date (26 of those, 01/06/02 thru 12/22/02). I want to determine the current pay period based on the date, and post that pay period number (such as pay period 6)to a specific cell. Can anyone tell me how to go about this?...Hope this was not too confusing.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I guess it is a bit confusing. I can't figure out whether you want to calculate the vacation accrual days or if you want to put the pay period in a cell.

    Tell us where you want to calculate the accrual days or tell us what the dates covered by pay period 1.

    I think that ought to help us sort it out.
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Bel Air, MD, USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the response.
    I actually want to "examine" the list of pay period dates (in Excel), determine which is the current pay period, then calculate the vacation accrual from that information for each employee.The first pay period end date was 01/06/02, the second was 01/20/02, and continues every 14 days until it reaches 12/22/02 which is the last pay period end date in 2002. Each employee will accrue vacation at a different rate dependent upon how may years of service he/she has with the company.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,496
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default


    You could create a table of rates and
    edit a formula that captures relevant information and completes the calc.

    accrual starts A1
    User Defined Function Weeks calcs # of weeks
    (see post I made a few days ago).

    Could probably use countif or ...
    A4 person's name
    D1 date person started
    rRates range of rates
    Lookup determines relevant column
    Vlookup determines relevant rate

    weeks*rate = amount

    =Weeks(A1,TODAY())*VLOOKUP(A4,rRates,LOOKUP(DATEDIF(D1,TODAY(),"y"),{0,5,10;0,2,3}),1)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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