IF Forumla Help Please

[scrabble42]

Need help with this please. Not that great with excel and can't get my head wrapped around it.

Column S is age dependent (CPP only applies if individual is over 18 at payroll end date). Therefore need column S to determine age and if applicable then use the formula as shown in S6 and if not just show -. I am manually removing the formula when necessary but want to simplify and remove the human error factor. Any help appreciated. Thanks!

Payroll 2007 Wrkg03.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	PayEnding	21-Mar-07		EMPLOYEEPAYROLLINFORMATION															21-Mar-07
2	1	2-Jan-00	3	5	6	7		8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
3						Earnings										Deductions
4	EmployeeID	DateofBirth	Name	RateofPay	RateofPay(2)	RegularHours	StatHolidayHrs	RegHrs.Rate(2)	RegularEarnings	RegEarnings(2)	BankedOvertimeHrs	OvertimeHours	OvertimeEarnings	VacationPay	GrossPay	Federal	Add'lTax	Prov.	CPP	EI	Misc.	GrossDeductions	NetPay
5	1
6	2	6-Mar-76	JeanJeans	9.00	-				-	-			-	-	-	-		-	-	-		-	-
7	3	1-Jan-92	JoeBlow	15.00	-				-	-			-	-	-	-		-		-		-	-
21 Mar

 

[kmillen]

Try this.

=IF(INT(TEXT(YEAR(NOW()) - YEAR(B6), "##")) >= 18, IF(O6 > 134.62, (O6 - 134.62) * 4.95%, 0), "-")

 

[scrabble42]

Thanks but unfortunately, it gave me a 0 for the individual who was old enough and a $ amount for the person over 18 (S6). Opposite of what I require. Any suggestions as to what should be changed. By the way, I just typed in bogus numbers in relative columns to get a calc.

 

[scrabble42]

And I forgot to mention again KMillen, the calc is not dependent just on year, it is actual date specific (day, month, year) since birthday may be mid year and the calc would apply from that date forward.

 

[Peter_SSs]

scrabble42

Try this :

=IF(DATEDIF(B6,$B$1,"y")<18,"-",your formula here)

 

[scrabble42]

Thanks Peter, it appears to be working, but not confident that it is basing the calc on 18 as of payroll end date. I can see it on 18 as of the year, but as I said, need it for the month, day as well.

 

[Peter_SSs]

Thanks Peter, it appears to be working, but not confident that it is basing the calc on 18 as of payroll end date. I can see it on 18 as of the year, but as I said, need it for the month, day as well.

A little bit of testing should give you that confidence. have a look at this sheet:

Excel Workbook
	A	B	C	D
1	Pay Ending	27/03/2007
2
3
4		Date of Birth	Age at Pay Ending Date
5		25/03/1989	At least 18
6		26/03/1989	At least 18
7		27/03/1989	At least 18
8		28/03/1989	less than 18
9		29/03/1989	less than 18
10		30/03/1989	less than 18
11
Ages

 

[scrabble42]

Okay, appreciate your efforts in dealing with a blank brain here Peter. Checked it out, and you can see below what I am getting for results.

Payroll 2007 Wrkg03.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	PayEnding	21-Mar-07		EMPLOYEEPAYROLLINFORMATION															21-Mar-07
2	1	2-Jan-00	3	5	6	7		8	9	10	11	12	13	14	15	16	17	18	19	20
3						Earnings										Deductions
4	EmployeeID	DateofBirth	Name	RateofPay	RateofPay(2)	RegularHours	StatHolidayHrs	RegHrs.Rate(2)	RegularEarnings	RegEarnings(2)	BankedOvertimeHrs	OvertimeHours	OvertimeEarnings	VacationPay	GrossPay	Federal	Add'lTax	Prov.	CPP	EI
5	2	19-Mar-89	JeanJeans	9.00	-	35.00			315.00	-			-	12.60	327.60			-	9.5575	5.90
6	3	20-Mar-89	JoeBlow	15.00	-	35.00			525.00	-			-	21.00	546.00			20.40	20.368	9.83
7	4	21-Mar-89	JeanJeans	11.00	-	35.00			385.00	-			-	15.40	400.40			3.90	13.161	7.21
8	5	22-Mar-89	JoeBlow	13.67		35.00			478.33	-			-	19.13	497.47			14.60	-
9	6	23-Mar-89	JeanJeans	14.67		35.00			513.33	-			-	20.53	533.87			18.60	-
21 Mar

 

[Peter_SSs]

Is there a question/problem here?

Rows 5, 6 and 7 have dates of birth that produce an age of at least 18 and those rows are producung results in column S. Rows 8 and 9 have dates of birth that produce an age less than 18 and those rows have an "-" in column S. Isn't that what you wanted?

 

[scrabble42]

DUH! Knew it was time to hug a pillow instead of still messing with that. Of course you are right and it is working! Thanks so much.