IF Forumla Help Please

scrabble42

New Member
Joined
Jan 13, 2007
Messages
38
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
4EmployeeIDDateofBirthNameRateofPayRateofPay(2)RegularHoursStatHolidayHrsRegHrs.Rate(2)RegularEarningsRegEarnings(2)BankedOvertimeHrsOvertimeHoursOvertimeEarningsVacationPayGrossPayFederalAdd'lTaxProv.CPPEIMisc.GrossDeductionsNetPay
51
626-Mar-76JeanJeans9.00------------
731-Jan-92JoeBlow15.00-----------
21 Mar
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this.

=IF(INT(TEXT(YEAR(NOW()) - YEAR(B6), "##")) >= 18, IF(O6 > 134.62, (O6 - 134.62) * 4.95%, 0), "-")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
scrabble42

Try this :
Code:
=IF(DATEDIF(B6,$B$1,"y")<18,"-",your formula here)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
4EmployeeIDDateofBirthNameRateofPayRateofPay(2)RegularHoursStatHolidayHrsRegHrs.Rate(2)RegularEarningsRegEarnings(2)BankedOvertimeHrsOvertimeHoursOvertimeEarningsVacationPayGrossPayFederalAdd'lTaxProv.CPPEI
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top