I have a spreadsheet that includes the following: 2 columns (d and e)
with a beginning and end date, a column with a dollar amount (rent: g)
and. 24 columns across (Jan - Dec for two years:- n - ak). The 24
columns across contain the date ie. (01/01/2012, 02/01/2012, and so
on).

I am wanting to compare the range of D - E with each column across.
When it meets the criteria it will put the amount in G in each column
that is BETWEEN the date range. For example:

if the beginning date is 01/01/2012 (d), the ending date is 06/01/2013
(e), and rent is $3000 (g), I would like Jan - Dec 2012 and Jan - Jun
2013 to be populated with $3000. The remaining columns would be 0.

I have tried the following, but will only work for 2012.



=IF(AND(MONTH($D:$D)<=MONTH(T$9),YEAR($D:$D)<=YEAR(T$9),MONTH($E:
$E)>=MONTH(T$9),YEAR($E:$E)=YEAR(T$9)),$G:$G,0)



What am I doing wrong? Any help is much appreciated.



Thanks

On Aug 18, 7:35*am, Tammy Robinson <trobin...@ntsdevco.com> wrote:
> I have a spreadsheet that includes the following: 2 columns (d and e)
> with a beginning and end date, a column with a dollar amount (rent: g)
> and. 24 columns across (Jan - Dec for two years:- n - ak). The 24
> columns across contain the date ie. (01/01/2012, 02/01/2012, and so
> on).
>
> I am wanting to compare the range of *D - E with each column across.
> When it meets the criteria it will put the amount in G in each column
> that is BETWEEN the date range. For example:
>
> if the beginning date is 01/01/2012 (d), the ending date is 06/01/2013
> (e), and rent is $3000 (g), *I would like Jan - Dec 2012 and Jan - Jun
> 2013 *to be populated with $3000. The remaining columns would be 0.
>
> I have tried the following, but will only work for 2012.
>
> =IF(AND(MONTH($D:$D)<=MONTH(T$9),YEAR($D:$D)<=YEAR(T$9),MONTH($E:
> $E)>=MONTH(T$9),YEAR($E:$E)=YEAR(T$9)),$G:$G,0)
>
> What am I doing wrong? Any help is much appreciated.
>
> Thanks

No need to split the month and year to compare, you can compare the
start and end month to the 24 month headers.

=IF(AND($D2<=I$1,$E2>=I$1),$G2,0)

D E F G H I H K L M N
1 Beginning Date End Date Rent 1/1/2012 2/1/2012 3/1/2012 4/1/2012
5/1/2012 6/1/2012 7/1/2012
2 2/1/2012 6/1/2012 3000 0 3000 3000 3000 3000 3000 0

Formatting messing up the alignment but you get the idea.

Praxx - Thanks so much. That seems to do the trick! I have been going
crazy. making it hard then it should be I guess.