 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Vacation accrual Spreadsheet
I am working on a spread sheet for my Company. I have looked at many examples but I am still not finding what I need with the specific perimeters involved. the facts: PDO accrual rate increases every employee anniversary. PDO accrues every weekly pay period worked. PDO accrues after 90 days of employment. PDO max accrual is 120 hours. PDO incremental rate accrual as follows: 1st year----2.0 hrs per pay period after first year-----2.15 after second yr----2.3 after third year----2.46 after fourth year--2.61 after fifth year-----2.76 after sixth year----2.92 after seventh yr---3.07 after eigth yr------3.23 =IF(TODAY()-D5<90,0,MIN(120,DATEDIF(D5,TODAY(),"M")*CHOOSE(SUMPRODUCT(--(DATEDIF(D5,TODAY(),"M")>{0;1;2;3}*12)),2,2.15,2.3,2.46,2.61,2.76,2.92,3.07,3.23)))  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I am working on a spread sheet for my Company. I have looked at many >examples [quoted text clipped - 16 lines] > after seventh yr---3.07 > after eigth yr------3.23 Bob, Thanks for the formula. I copied it into a spreadsheet but im not sure how the cells should be set up to read to get the result. if I could impose upon you good nature....thanks > =IF(TODAY()-D5<90,0,MIN(120,DATEDIF(D5,TODAY(),"M")*CHOOSE(SUMPRODUCT(--(DATEDIF(D5,TODAY(),"M")>{0;1;2;3}*12)),2,2.15,2.3,2.46,2.61,2.76,2.92,3.07,3.23))) > [quoted text clipped - 18 lines] > > after seventh yr---3.07 > > after eigth yr------3.23 Tim, In my tests, I just set the employee's start date in D5, and then plugged in that formula. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob, > Thanks for the formula. I copied it into a spreadsheet but im not sure how [quoted text clipped - 23 lines] >> > after seventh yr---3.07 >> > after eigth yr------3.23 http://spreadsheets.google.com/a/wirelesscenterinc.com/pub?key=pkeqzbgaxnSscdhJp DBvfNA&output=html Here is a link to see the shet in google docs. I inserted your formula it doenst work in google. And in Excel it shows 120 instead of how many days or hours are accumulated through a certain date. > Tim, > [quoted text clipped - 28 lines] > >> > after seventh yr---3.07 > >> > after eigth yr------3.23 It needs a login, and I don't have one. Perhaps the 120 is because they have been there long enough to accrue the max, 120. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > http://spreadsheets.google.com/a/wirelesscenterinc.com/pub?key=pkeqzbgaxnSscdhJp DBvfNA&output=html > [quoted text clipped - 37 lines] >> >> > after seventh yr---3.07 >> >> > after eigth yr------3.23 Thanks, but I may not have been clear. I am having a difficult time getting this issue solved > It needs a login, and I don't have one. > [quoted text clipped - 42 lines] > >> >> > after seventh yr---3.07 > >> >> > after eigth yr------3.23 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.