# Multiple If(AND

M

#### Micki

1/1/2008 2/1/2008 3/1/2008 4/1/2008
1/15/2008 2/15/2008 3/15/2008 4/15/2008
1/31/2008 2/29/2008 3/31/2008 4/30/2008
Column - C G H I J K L
Month Start Date End Date January February March April
5,821.83 1/1/08 4/15/08

Trying to write combinations of If(And statements whereby salary (column C)
for a person will start at their start date and continue across all months
until there is an end date, if there is one at all. I have tried so many
iterations, breaking the formula up in to pieces to see which part doesn't
work etc. and I just keep getting stuck. I need to calculate for 1/2 month
intervals if their start or end date is in the middle of the month. Thank
you.

J

#### John C

Will take a few things for granted....rows 1-3 have the 1st, 15th, and final
day of each month for columns I thru T. Row 4 will have headers such as
Month, Start Date, End Date, January thru December. First row of data is 5. I
am also assuming that all start dates and end dates will be either the 1st,
15th, or final day of the month.

The formula that I put in cell I5, and copied through T5 (and then down as
many rows as you wish), is as follows:

=IF(AND(\$G5<=I\$1,OR(\$H5>=I\$3,\$H5="")),\$C5,IF(OR(AND(\$G5<=I\$2,OR(\$H5="",\$H5>=I\$3)),AND(\$G5<=I\$1,\$H5>=I\$2)),\$C5/2,""))

This first checks to see if the person has the full month's salary, by
checking the start date is on or before the first of the month (\$g5<=i\$1) and
the end date is either on or after the last day of the month (\$h5>=i\$3) or no
end date at all (\$h5=""). If the full month, it populates the full salary
(\$c5).

It then checks to see if the start date is on the 15th of the month
(\$g5<=i\$2) as well as having an end date on or after the last of the current
month (\$h5>=i\$3) or no end date at all (\$h5="") OR IF THE start date is on or
before the 1st day of the month (\$g5<=i\$1) and the end date on or beyond the
15th of the month (\$h5>=i\$2).

M

#### Micki

Can I do the same thing, but include the condition that if the salary is
negative and the start date is always beginning of year, then enter a zero
until we get to the first month after the end date, then subtract the monthly
salary through end of year? Too many nested functions?

J

#### John C

I am not understanding exactly what you are trying to accomplish. A negative
salary?