compare date range with date in columns and input data from another cell

[Tammy Robinson]

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

 

[Praxx]

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.

 

[Tammy Robinson]

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