New to Excel with Question
I am new to Excel and thought this a good opportunity to learn something about Excel. The Project I am working on requires figuring out the Vacation hours earned after being employed X number of years. If present year minus year of employment is greater than (or less than) X, then hours earned equals Y. I hope this is clear enough!? Any direction would be appreciated. So far I have been using the Help File but have much to learn about Formulas. Thank you for any assistance. -- PaulStroik ------------------------------------------------------------------------ PaulStroik's Profile: http://www.excelforum.com/member.php...o&userid=24640 View this thread: http://www.excelforum.com/showthread...hreadid=382262 |
Hi!
Here's one way. Assume the vacation ranges a 1 yr...........40 hrs 5 yrs.........80 hrs 10 yrs.....120 hrs 20+ yrs...160 hrs Employee hire date is in cell A2: =VLOOKUP(YEAR(TODAY())-YEAR(A2),{1,40;5,80;10,120;20,160},2,1) If the date difference ( in years) is less than 1 yr the formula will return #N/A. Any difference in years greater then 20 will return 160. That answers your question as posed. However, just about every employer has their own method for determining vacation earned. Some base it on the employees hire date anniversary. Consider this example: Employee hire date: 12/31/2004 Todays date: 1/1/2005 Using this example the above formula would return 40 even though the total difference in dates is only 1 day. Also, if an employer bases vacation time on anniversary dates how does an employee take 40 hrs of vacation starting on 12/31/2005? Hopefully, some common sense is applied but you never can tell with some companies! Biff "PaulStroik" wrote in message ... I am new to Excel and thought this a good opportunity to learn something about Excel. The Project I am working on requires figuring out the Vacation hours earned after being employed X number of years. If present year minus year of employment is greater than (or less than) X, then hours earned equals Y. I hope this is clear enough!? Any direction would be appreciated. So far I have been using the Help File but have much to learn about Formulas. Thank you for any assistance. -- PaulStroik ------------------------------------------------------------------------ PaulStroik's Profile: http://www.excelforum.com/member.php...o&userid=24640 View this thread: http://www.excelforum.com/showthread...hreadid=382262 |
All times are GMT +1. The time now is 08:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com