+ Reply to Thread
Results 1 to 3 of 3

PTO Accrual

  1. #1
    Registered User
    Join Date
    10-18-2011
    Location
    clear spring md
    MS-Off Ver
    Excel 2007
    Posts
    2

    PTO Accrual

    I am trying to set up a spreadsheet to track PTO. I have attached my spreadsheet. first tab is the accrual rate and the second tab is accumulated time off. I have a second spreadsheet that i will connect as a pivot table to give me actual. I am looking for a formula to help me with the accumulated based on accrual rate. Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: PTO Accrual

    Hey Scampbell;

    Took me a little while to get it... And even then, I'm having some slight rounding errors... but here you go:

    =SUMPRODUCT((12*(YEAR(TODAY())-YEAR(B2))+MONTH(TODAY())-MONTH(B2)> {0;12;25;37;49}) * (12*(YEAR(TODAY())-YEAR(B2))+MONTH(TODAY())-MONTH(B2) - {0;12;25;37;49}) * {7.33;1.33;1.33;1.33;2.67})

    Where B2 and B3 are the Hire Dates....

    There's a SLIGHT rounding error, so you may want to put a rounding function around the whole thing.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Registered User
    Join Date
    10-18-2011
    Location
    clear spring md
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: PTO Accrual

    Thank you so much1!! this will work

+ 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