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

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
Joined
Jun 8, 2007
Messages
115
Try this.

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

scrabble42

New Member
Joined
Jan 13, 2007
Messages
38
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
Joined
Jan 13, 2007
Messages
38
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
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
scrabble42

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

scrabble42

New Member
Joined
Jan 13, 2007
Messages
38
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
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 13, 2007
Messages
38
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,397
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 13, 2007
Messages
38
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.
 

Forum statistics

Threads
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.
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
Top