# Need Simple Vacation Accrual Spreadsheet

1. ## 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!!!

2. ## Re: Need Simple Vacation Accrual Spreadsheet

I did it with a table, nested IFs and MIN:

Attachment 261091
Note: the formulas are entered as arrays using Control+Shift+Enter instead of just hitting enter as normal

3. ## Re: Need Simple Vacation Accrual Spreadsheet

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!!!

4. ## Re: Need Simple Vacation Accrual Spreadsheet

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!

5. ## Re: Need Simple Vacation Accrual Spreadsheet

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

6. ## Re: Need Simple Vacation Accrual Spreadsheet

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!

7. ## Re: Need Simple Vacation Accrual Spreadsheet

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,))))))

8. ## Re: Need Simple Vacation Accrual Spreadsheet

Sorry for the bad attachment earlier.

I put the hours to days conversion into a small table, and build an array formula around it.

=MAX(IF(\$C\$5:\$C\$13<=(TODAY()-\$F7),IF((TODAY()-\$F7)>\$C\$5:\$C\$13,\$A\$5:\$A\$13)))

This is an array formula, entered with Ctrl+Shift+Enter

Attachment 261679

9. ## Re: Need Simple Vacation Accrual Spreadsheet

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.

10. ## Re: Need Simple Vacation Accrual Spreadsheet

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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1