Nested if formula

[Jurek]

Need help with building nested if formula.
Have following columns: date of hire, date of separation, date of salary
increase, old pay, new pay, amount of increase, and months of the year.
Trying to build statement that would display monthly salary for months worked
(show blanks outside this range. For example if employee worked from march to
august, display salary for the same months. Outside this range show zero.
Also if in may emplyee got increase in july show old salary for march thru
june and for july thru august a new wage. Is this doable. Please help.
Jurek

 

[Bill Pfister]

Jurek, this is easier to show in a workbook, but here's an explanation. Let
me know if you'd like the example.

C5: hire date
D5: separation date
E5: salary increase date
F5: old pay amount
G5: new pay amount
H5: amount of increase amount (not needed here)

J3-U3: date headers (1/1/06, 2/1/06, ... , 12/1/06)

J5-U5: put this formula in J5 and copy through U5:

=IF( AND( J$3>$C5, J$3<$D5 ), IF( J$3>=$E5, $G5, $F5 ), 0 )

 

[Jurek]

Bill:
Thanks. It works. I managed to solve the problem with a variation
=IF(HireDate=0,0,IF(SepDate<Jan,0,IF(Jan<HireDate,"",IF(IncDate=0,OldWage,IF(
Jan>IncDate,NewWage/12,OldWage/12))))).
But your formula is cleaner. I am dividing new and old salary by 12 to
display montlhy amounts.
The one caveat is that the separation date can not be an empy field. It has
to have some kind of data. Is there a way of displaying the results for null
data for separation. Because as is is now I have to "terminate" an employee
to get his/hers salary. If the formula could capture this data (salary) for
an active employee(no separation date) it would be great. Right now for
active employee I must project a termination date far into the future.
But thanks for your help and reply.
Jurek

 

[Jurek]

Bill:
I have one more question. What is gt and lt in you formula?
Jurek

 

[Dave Peterson]

The interface that you're using to read messages is showing the greater than and
less than symbols as gt and lt.

I use a plain text newsreader and Bill's message shows up very nice for me.

 

[Jurek]

Thanks Bill
Jurek

 

[Bill Pfister]

Sorry for the late reply; I've been traveling for the holidays.

The following formula will handle a lack of separation date. Let me know if
this doesn't cover what you need.

=IF( ISBLANK( $B5 ), 0, IF( ISBLANK( $C5 ), IF( I$3>$B5, $E$5, 0 ), IF( AND(
I$3>$B5, I$3<$C5 ), IF( I$3>=$D5, $F5, $E5 ), 0 ) ) )

Bill