Calculating Vacation and sick leave

[New2excel]

Hello Excel gurus!

I am new to Excel, but I can do simple functions and macros. I have been asked by my boss to create a simple spreadsheet that will calculate Vacation time and sick leave time as QuickBooks has been giving them trouble. I have searched the message boards but have not found what I need to do this. Or I have not understood how it is done. Any help will be greatly appreaciated.

Vacation is calculated by "date hired" anniversary dates:
First anniversary = 40 hours vacation time (automatic update on first anniversary date needed)
Second anniversary and every anniversary date thereafter = 80 hours vacation time (automatic update on each anniversary date needed)

This is the closest I can come to understanding the code to do this.
I'm not sure I am close to the right track
First somehow count the anniversary dates in D column then something like =IF(D6<1,D6,0)(D6=1,D6*40)(D6>1,D6*80) ???

Sick leave is calculated by number of pay periods times .92 hours
Pay periods are Thursday through Wednesday.
If employee starts in the middle of the pay period use previous Thursday as beginning first pay period
Update pay periods automatically by entering pay period end date in B2 or by using the TODAY() function
===========================
Here are the column headings I think I need after the entered "Pay Period End Date:" or use of the =Today() function.

Column Headings:
A) Employee Name:
B) Date Hired:
C) Number of Pay periods Worked:
D) Vacation Hours Accrued:
E) Sick Leave Hours Accrued:
F) Vacation Hours Used:
G) Sick Leave Hours Used:
H) Vacation Hours Balance:
I) Sick Leave Hours Balance:

I hope I have explained myself clearly......

P.S. Is there a good beginners book you would recommend?
Thanks again, for any help you can offer.

 

[New2excel]

Thanks, Taz, I will check it out thursday when I go back to work.

 

[New2excel]

Well, I went into work on my own time today.... yes, it's a family owned business so I can work for free... ha ha ha

I did the add-ins Analysis Tool Pak and it worked GREAT!!!
Thanks for everyones help!!

I did have to tweak it a bit. Column E2 [E2: =MIN(80,40*(INT(D2/52)))] Didn't work correctly and I didn't understand the MIN part of the formula. So all I did was change it to [E2: =(80*(INT(D4/52))-40)] It wasn't doing the first anniv at 40 hours earned and every anni after that at 80 hours earned (example: two years would have been 40 for the 1st plus 80 for the 2nd equalling 120 hours) This seems to work okay, I don't know if it will cause problems down the road or not.

But now I have to figure out how to tell it that only 2 weeks (80 hours) are allowed to roll over into the next year. So anything >80 = 80 ???

I also set up individual sheets that hold the employees information where I can post the hours used in vacation time or sick leave time. It will keep a running total of used hours and I have it posting the totals to this sheet automatically. It worked!! ha ha I love this program!!

Thanks again for all the unselfish help you all give on these message boards. You are wonderful to help teach the rest of us!
Suz