#### scrabble42

##### New Member
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
ABCDEFGHIJKLMNOPQRSTUVW
1PayEnding21-Mar-07EMPLOYEEPAYROLLINFORMATION21-Mar-07
212-Jan-003567891011121314151617181920212223
3EarningsDeductions
51
626-Mar-76JeanJeans9.00------------
731-Jan-92JoeBlow15.00-----------
21 Mar

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### kmillen

##### Board Regular
Try this.

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

#### scrabble42

##### New Member
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

##### New Member
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

##### MrExcel MVP, Moderator
scrabble42

Try this :
Code:
``=IF(DATEDIF(B6,\$B\$1,"y")<18,"-",your formula here)``

#### scrabble42

##### New Member
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

##### MrExcel MVP, Moderator
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
ABCD
1Pay Ending27/03/2007
2
3
4Date of BirthAge at Pay Ending Date
525/03/1989At least 18
626/03/1989At least 18
727/03/1989At least 18
828/03/1989less than 18
929/03/1989less than 18
1030/03/1989less than 18
11
Ages

#### scrabble42

##### New Member
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
ABCDEFGHIJKLMNOPQRST
1PayEnding21-Mar-07EMPLOYEEPAYROLLINFORMATION21-Mar-07
212-Jan-003567891011121314151617181920
3EarningsDeductions
5219-Mar-89JeanJeans9.00-35.00315.00--12.60327.60-9.55755.90
6320-Mar-89JoeBlow15.00-35.00525.00--21.00546.0020.4020.3689.83
7421-Mar-89JeanJeans11.00-35.00385.00--15.40400.403.9013.1617.21
8522-Mar-89JoeBlow13.6735.00478.33--19.13497.4714.60-
9623-Mar-89JeanJeans14.6735.00513.33--20.53533.8718.60-
21 Mar

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### New Member
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.

Replies
9
Views
511
Replies
0
Views
296
Replies
5
Views
200
Replies
2
Views
1K
Replies
3
Views
577

1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back