Hi Folks,
I'm having a senior moment here and would appreciate any help. I have
a formula to return a monthly salary in a budgeting sheet. It
compares three dates; a startdate (in K3), when the person started
work, the date at the end of a month(in V2), and a leavedate when they
left employment(in K3). It has to give "NS" (not started) in any
month before they were employed, their budgeted salary
(ROUND($O3/12,1) for each month employed, and "left" for any month
that they are no longer employed. By definition an employee cannot
have a leavedate without having a startdate, but it is possible for an
employee to have a short-term contract so that they have "NS", a few
month's salary and "left" within one financial year.
The formula that I have, correctly returns NS until I enter a
leavedate in cell K3 whereapon it returns a monthly salary even though
they have not started employment. Can anyone correct my formula so
that it gives the right return in each case. My mind seems to be
completely boggled.


= IF($K3="",IF($J3<$V$2,ROUND($O3/12,1),"NS"),IF($K3$V
$2,ROUND($O3/12,1),"left"))

On Feb 5, 1:59*pm, Dave wrote:
I have a formula to return a monthly salary in a budgeting sheet. *It
compares three dates; a startdate (in K3), when the person started
work, the date at the end of a month(in V2), and a leavedate when they
left employment(in K3). *It has to give "NS" (not started) in any
month before they were employed, their budgeted salary
(ROUND($O3/12,1) for each month employed, and "left" for any month
that they are no longer employed.
[....]
= IF($K3="",IF($J3<$V$2,ROUND($O3/12,1),"NS"),IF($K3$V
$2,ROUND($O3/12,1),"left"))

I think you have the right idea. The problem is likely the extra
parenthesis after "NS". But I did not study your formula to see if
its logic fits your requirement. I think it can be simplified.
Assuming that J3, not K3, is the starting date, try:

=if(V2<$J3, "NS", if(or($K3="",V2<=$K3), round($O3/12,1), "left"))