IF formula question - nesting multiple functions.

[kraymond]

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!

 

[JoeU2004]

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]

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