Jump to content


Photo

Formula for vacation accruals


  • Please log in to reply
1 reply to this topic

#1 Dakota

Dakota

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 14 July 2017 - 05:46 AM

Hello,

I have a spreadsheet with employee dates of hire however have different accrual policies.  Trying to find out based on years of service vacation accrual for each policy.  Have attempted various formulas can not get it right. 

 

#1 Policy  Effective 7/1/17

0-6 months up to and including last day of the 6th month of service = 0 hours

1st day of the month following six months of employment to 1 year =  1.54 hours every payroll

1 to 2 years = 1.54 hours every payroll

2 to 5 year = 3.08 hours every payroll

5 years and over 4.62 hours every payroll

 

#2 Policy Effective 4/1/16

1st day of the month following 6 months of employment to 1 year = 1.54 hours

1 to 2 years = 1.54 hours every payroll

2 to 5 year = 3.08 hours every payroll

5 years and over 4.62 hours every payroll

 

#2 Policy Effective prior to 4/1/16

After 1 year of service 1.54 hours every payroll

After 2 years of service 3.08 hours every payroll

After 5 years of service 4.62 hours every payroll

 

 

This policy applies to employees hired after 4/1/16

1st day of the month following 6 months of employment to 1 year = 1.54 hours

1 to 2 years = 1.54 hours every payroll

2 to 5 year = 3.08 hours every payroll

5 years and over 4.62 hours every payroll

 


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 15 July 2017 - 04:33 PM

Hi Dakota,

 

It does look like this should be possible using the IF function, although it may be a rather complex formula. Splitting the formula into several columns is usually a good way to cut down on the complexity and create a more manageable system.

 

A very similar system is created in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function. I'd recommend viewing this lesson if you haven't already, as it should give you a good overview of the skills you'll need.

 

Unfortunately I can't give you a more specific answer without seeing an example of the data you are working with. If attach an example workbook I can try to give you a more in-depth answer. You can find instructions on how to attach workbooks here.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users