Here's my Spreadsheet:

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.

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(AN D($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).

--
John C


"Micki" wrote:

Here's my Spreadsheet:

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.

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?

"John C" wrote:

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(AN D($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).

--
John C


"Micki" wrote:

Here's my Spreadsheet:

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.

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


"Micki" wrote:

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?

"John C" wrote:

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(AN D($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).

--
John C


"Micki" wrote:

Here's my Spreadsheet:

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.