IF formula question - nesting multiple functions.
 

Hello, I'm having trouble with writing a formula to calculate sick leave
payoff. If column A is hire date, column C is hour type (sick, vacation,
holiday), column C is hours of sick leave accrued, and column D is hourly
rate, I need to calculate: If the hire date is = 5 years, then sick leave
payout is 25% of accrued sick hours. I've got an IF function already for
=IF(G11="Sick Leave",(H11*0.25)*I11,0), but need to also include a function
to return the value if the date is greater than 5 years, and to return 0 if
the hire date is within 5 years. I appreciate any help!

IF formula question - nesting multiple functions.
 

"kraymond" wrote:
I need to calculate: If the hire date is = 5 years, then sick leave
payout is 25% of accrued sick hours. I've got an IF function already for
=IF(G11="Sick Leave",(H11*0.25)*I11,0), but need to also include a
function to return the value if the date is greater than 5 years

One way:

=if(and(G11="sick leave",datedif(A1,today(),"y")=5), H11*I11*25%, 0)

But you might want to replace TODAY() with a reference to the cell that
contains the date of the calculation. That is usually not necessarily
"today"; instead it is a payroll date.

Note: You wrote G11, H11 and I11. I don't see those columns mention in
your design outline below. But I presume you can sort out the disparity.


----- original message -----

"kraymond" wrote in message
...
Hello, I'm having trouble with writing a formula to calculate sick leave
payoff. If column A is hire date, column C is hour type (sick, vacation,
holiday), column C is hours of sick leave accrued, and column D is hourly
rate, I need to calculate: If the hire date is = 5 years, then sick leave
payout is 25% of accrued sick hours. I've got an IF function already for
=IF(G11="Sick Leave",(H11*0.25)*I11,0), but need to also include a
function
to return the value if the date is greater than 5 years, and to return 0
if
the hire date is within 5 years. I appreciate any help!

IF formula question - nesting multiple functions.
 

Excellent, I appreciate the input, and I was able to get my formula to
calculate correctly. Thanks again!

"JoeU2004" wrote:

"kraymond" wrote:
I need to calculate: If the hire date is = 5 years, then sick leave
payout is 25% of accrued sick hours. I've got an IF function already for
=IF(G11="Sick Leave",(H11*0.25)*I11,0), but need to also include a
function to return the value if the date is greater than 5 years

One way:

=if(and(G11="sick leave",datedif(A1,today(),"y")=5), H11*I11*25%, 0)

But you might want to replace TODAY() with a reference to the cell that
contains the date of the calculation. That is usually not necessarily
"today"; instead it is a payroll date.

Note: You wrote G11, H11 and I11. I don't see those columns mention in
your design outline below. But I presume you can sort out the disparity.


----- original message -----

"kraymond" wrote in message
...
Hello, I'm having trouble with writing a formula to calculate sick leave
payoff. If column A is hire date, column C is hour type (sick, vacation,
holiday), column C is hours of sick leave accrued, and column D is hourly
rate, I need to calculate: If the hire date is = 5 years, then sick leave
payout is 25% of accrued sick hours. I've got an IF function already for
=IF(G11="Sick Leave",(H11*0.25)*I11,0), but need to also include a
function
to return the value if the date is greater than 5 years, and to return 0
if
the hire date is within 5 years. I appreciate any help!