Great Forum!
I searched, but was unable to find something specfically for what I am looking for, which leads me to believe it may not be possible or I am not wording my search appropriately, which negates results.
My employer has come come up with some weird calculation to determine if employees get paid out on accumulated vacation leave or not. No one can figure it out because it is something you only do once a year so I was going to try to do an excel calculation to make it ez.
1.The first step was to put in today's date which I figured out to be NOW().
2.The employee would put in his/her date of hire
3.A cell would automatically figure out the employees years of service. (Year/Month/Day)
4.Based on the years of service, a monthly accrual figure would populate in the next cell, which could be 6 different values.
5.Additional calculations will be done, which include math and multiplying and I will be able to handle those.
My first problem is step 3:
When I put a date in the date of hire I get the Year, month and the day, but the day is in a decimal format. I cannot figure out why.
My Second problem is step 4:
I cannot figure out how to make it so Excel recognizes the years of service as a value. And when it does how to make that value a If and Then fuction with 6 values. Here are the functions I am trying to accomplish depending on where the years of service fall. Book1.xlsx
<1.5 Years = "Don't Qualify"
>1.5 years to < 5years = 96
>5 Years to <10 Years = 120
>10 Years to <15 Years = 160
>15 Years to <20 Years = 184
>20 Years to <25 Years = 192
>25 Years = 200
I attached what I have so far. Thank you.
Bookmarks