Need Simple Vacation Accrual Spreadsheet

Hello!

I am new to this Forum, and only know basic Excel functions. I'm an Excel for Dummies candidate for sure.

I would like to set up a Vacation Accrual Spreadsheet, and have modeled a formula from this Forum to get me started. The previous person here used paper and a pencil! Ouch! I'm new at this job, and want to simplify it but need a little help.

I thought the formula was similar to my needs based on a simple and "usual" vacation accrual, but I delved into the "paper" and it is different. The modeled formula is:

40 hours after one-year of service,
80 hours after 2-15 years of service,
120 hours after the 15th year.
B2 column is when the employee started with the company

=IF(TODAY()-B2<365,0,IF(AND(TODAY()-B2>365,TODAY()-B2<730),40,IF(AND(TODAY()-B2>730,TODAY()-B2<5475),80,120)))

The formula is what I need but with a few additions and adjustments such as:

Full Time Employees:
40 hours after one-year of service
80 hours after two-years of service
88 hours after three years of service
96 hours after five years of service
104 hours after seven years of service
120 hours after ten years of service
144 hours after fifteen years of service
160 hours after 20 years of service
200 hours after 25 years of service

I'm sorry if this may not be posted the correct way. Thank you to all for any help!!!

This might be a bit clunky.. But it does work.. Im sure there is a better way to do it.. Try this it actually does the in between dates as well.. Like 1.5yrs or a12.2 and so on.. In cell a2 will be the time spent of service.. Or if this dosnt work. Try uploading a small sample of your sheet..

=if(and(a2>=1,a2<2),40,if(and(a2>=2,a2<3),80,if(and(a2>=3,a2<5),88,if(and(a2>=5,a2<7),96,if(and(a2>=7,a2<10),104,if(and(a2>=10,a2<20),144,if(and(a2>=20,a2<25),160,if(and(a2>=25),200,""))))))))

Originally Posted by mrbsbest

hello!

I am new to this forum, and only know basic excel functions. I'm an excel for dummies candidate for sure.

i would like to set up a vacation accrual spreadsheet, and have modeled a formula from this forum to get me started. The previous person here used paper and a pencil! Ouch! I'm new at this job, and want to simplify it but need a little help.

I thought the formula was similar to my needs based on a simple and "usual" vacation accrual, but i delved into the "paper" and it is different. The modeled formula is:

40 hours after one-year of service,
80 hours after 2-15 years of service,
120 hours after the 15th year.
B2 column is when the employee started with the company

=if(today()-b2<365,0,if(and(today()-b2>365,today()-b2<730),40,if(and(today()-b2>730,today()-b2<5475),80,120)))

the formula is what i need but with a few additions and adjustments such as:

Full time employees:
40 hours after one-year of service
80 hours after two-years of service
88 hours after three years of service
96 hours after five years of service
104 hours after seven years of service
120 hours after ten years of service
144 hours after fifteen years of service
160 hours after 20 years of service
200 hours after 25 years of service

i'm sorry if this may not be posted the correct way. thank you to all for any help!!!

Hello, hello!

The formula gives me a formula error at the end....if(and(a2>=25),200,"" I thought when I put it through a newer version of Excel at home, it was fine. For some reason, it gives me an error at work.

Thank you so much for your help!

oh by the way.. the end should look like this.. if(and(a2>=25),200,""))))))))

I forgot to report the parentheses...but, it still gives me an error on this part. I'm using a 2003 version of Excel. Can that be why? if(and(a2>=25),200,"" My spreadsheet doesn't like this: and(a2>=25),200,""

Thank you so much for your help!

it's possible .. im not sure.. ive never used 2003 but i just copied and pasted the formula to my spreadsheet and it works fine..

try this instead. the last (AND) function dosnt need to be there,

=IF(AND(A2>=1,A2<2),40,IF(AND(A2>=2,A2<3),80,IF(AND(A2>=3,A2<5),88,IF(AND(A2>=5,A2<7),96,IF(AND(A2>=7,A2<10),104,IF(AND(A2>=10,A2<20),144,IF(AND(A2>=20,A2<25),160,IF(A2>=25,200,))))))

Thank you so much both daffodil11 and xwarlock10x!!! I will try them tonight. Wish me luck! I think we need to upgrade our version of Excel for sure.

Thank you, thank you, thank you!!!! Both formulas work! I really appreciate the time you took to help me. I owe you both!