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.

 

[srburk]

A great book for beginners is Mr. Excel on Excel. You can find it on this site. There are other good books, but I carry that one with me whenever I'm working on something.

 

[just_jon]

Hi, hello and welcome to MrExcel -- it's a bit slow around here on weekends, and I can't stay now, but I wanted you to know you're not being ignored and, if you've not gotten a solution by tomorrow I'll be back and give it a try.

 

[New2excel]

Thank you, Srburk. I will purchase Mr. Excel!

Thank you, Just_Jon, I appreciate your reply.

I'm off to purchase "Mr. Excel"!!

 

[Tazguy37]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Pay& period end date	Name	DOH	# of pay periods worked	Vac hrs accrued	Sick leave hrs accrued	Vac hrs used	Sick leave hours used	Vac hrs balance	Sick leave hrs balance
2	7/7/2004	Employee A	5/3/2000	218	80	200.56	40	8	40	192.56
3	7/7/2004	Employee B	4/1/2004	14	0	12.88	0	8	0	4.88
4	7/7/2004	Employee C	7/1/2004	1	0	0.92	0	0	0	0.92
5	7/7/2004	Employee D	7/2/2004	0	0	0	0	8	0	-8
6	7/7/2004	Employee E	7/1/2003	53	40	48.76	16	24	24	24.76
7	7/7/2004	Employee F	7/1/2002	105	80	96.6	5	32	75	64.6
8	7/7/2004	Employee G	7/1/2001	157	80	144.44	5	30	75	114.44
9	7/7/2004	Employee H	7/1/2000	209	80	192.28	7	31	73	161.28
10	7/7/2004	Employee I	7/1/1999	262	80	241.04	7	32	73	209.04
Sheet1

Formulas:
D2: =ABS(QUOTIENT(NETWORKDAYS(C2+(WEEKDAY(C2)>5)*7-WEEKDAY(C2)+5,A2),5))

E2: =MIN(80,40*(INT(D2/52)))

F2: =D2*0.92

I2: =E2-G2

J2: =F2-H2

All the formulas are just copied down the columns for as many employees as you have. This should be pretty close to what you asked for -- if something isn't quite right, please post back and I (or one of the gurus here) will attempt to fix the problem. Feel free to improve on what I've posted here -- I'm sure that first formula can be improved upon

 

[New2excel]

Thank you, Thank you, Thank you, Taz!

I will work on it at the office tomorrow, but it looks like you have it doing exactly what I needed.

I don't understand the code you used, so I can't wait to get my new book, "Mr. Excel On Excel".

Ever so grateful,
Suz

 

[New2excel]

Sorry about the green face..... I thought he was smiling, not sticking his tongue out....

Phooey! Taz......... it doesn't work here at the office! We have Excel 97. Would that make a difference?

I made sure all my columns and rows matched yours and that I didn't have anything different, but all I get is #NAME?

??????

 

[duplinguy]

You may to check to see if the Analysis Toolpack Addin is installed.

 

[New2excel]

Thanks, Dublinguy.

I did check the Add-Ins at work this afternoon and noticed that some were pre-checked: "Conditional Sum Wizard, Lookup Wizard, & Update Add-in links". Does this mean that these are the only ones not already added in?
If not, how can I tell what has already been added?

Dublin Ireland? My ancestors are from Ireland!

 

[Tazguy37]

To install the Analysis ToolPak
On the Tools menu, click Add-Ins.
If Analysis ToolPak is not listed in the Add-Ins dialog box, click Browse and locate the drive, folder name, and file name for the Analysis ToolPak add-in, Analys32.xll — usually located in the Microsoft Office\Office\Library\Analysis folder — or run the Setup program if it isn't installed.

Select the Analysis ToolPak check box.