Amortization Schedule-Need help with Dates

[Ricky]

Hello Experts,
Row 1 is formatted at mmm-yy. Columns A & B are formatted also as mmm-yy.
How do you write a formula for cell [D2] that looks at D1's date to see if
it is within the Column A2 and B2's date range. If true, divide C2 by
(B2-A2). If false, then leave blank.

So far, my formula is
=IF(MONTH(D$5)>=MONTH($A2),ROUND($F7/MONTH($D7-$C7),2),"")
The problem is that this formula doesn't take into account of the change in
year 2004...cause a month comparison error too.
Furthermore, how do you re-write this to not exceed the B2's end date?

I've tried
=if((and(year(D$1),month(D$1))>=(and(year($A1),month($A1))),True,False)
but that doesn't work either. It's giving me True results in all cells.


A B C D E F
G H I
1 Start End Total Oct-03 Nov-03 Dec-03 Jan-04 Feb-04
Mar-04
2 Nov-03 Feb-04 $100 $0 $25 $25 $25 $25
$0

Thanks in advance,
Ricky

 

[Bernie Deitrick]

Ricky,

Dates are simply numbers in Excel. Make sure you are using actual
date values. Then all you need to do is use

=IF(AND($D$1>=A2,$D$1<=B2),C2/(B2-A2),"")

You need to consider the implications of what happens when A2 or B2
are identically equal, and adjust the >= and <= accordingly.

HTH,
Bernie
MS Excel MVP

 

[Ricky]

Hi Bernie,
Thanks for you help. This is close. Because my header row is formatted as
ie. Nov-03 but embedded in there, it has a day. I need to compare only the
month and year of the header row with the dates in column A2 and B2
downwards. If I were to use your solution, it'll compare with full set of
dates (year,month and day). How do I modify your solution to to compare the
year and month only?

Thanks again,
Ricky

Ricky,

Dates are simply numbers in Excel. Make sure you are using actual
date values. Then all you need to do is use

=IF(AND($D$1>=A2,$D$1<=B2),C2/(B2-A2),"")

You need to consider the implications of what happens when A2 or B2
are identically equal, and adjust the >= and <= accordingly.

HTH,
Bernie
MS Excel MVP

Hello Experts,
Row 1 is formatted at mmm-yy. Columns A & B are formatted also as mmm-yy.
How do you write a formula for cell [D2] that looks at D1's date to see if
it is within the Column A2 and B2's date range. If true, divide C2 by
(B2-A2). If false, then leave blank.

So far, my formula is
=IF(MONTH(D$5)>=MONTH($A2),ROUND($F7/MONTH($D7-$C7),2),"")
The problem is that this formula doesn't take into account of the change in
year 2004...cause a month comparison error too.
Furthermore, how do you re-write this to not exceed the B2's end date?

I've tried
=if((and(year(D$1),month(D$1))>=(and(year($A1),month($A1))),True,False
)
but that doesn't work either. It's giving me True results in all cells.


A B C D E F
G H I
1 Start End Total Oct-03 Nov-03 Dec-03 Jan-04 Feb-04
Mar-04
2 Nov-03 Feb-04 $100 $0 $25 $25 $25 $25
$0

Thanks in advance,
Ricky

 

[Bernie Deitrick]

Ricky,

Dates must have days - so use a consistent day for all:

=IF(AND(DATE(YEAR($D$1),MONTH($D$1),1)>=DATE(YEAR(A2),MONTH(A2),1),DAT
E(YEAR($D$1),MONTH($D$1),1)<=DATE(YEAR(B2),MONTH(B2),1)),C2/(B2-A2),""
)

HTH,
Bernie
MS Excel MVP

Hi Bernie,
Thanks for you help. This is close. Because my header row is formatted as
ie. Nov-03 but embedded in there, it has a day. I need to compare only the
month and year of the header row with the dates in column A2 and B2
downwards. If I were to use your solution, it'll compare with full set of
dates (year,month and day). How do I modify your solution to to compare the
year and month only?

Thanks again,
Ricky

Ricky,

Dates are simply numbers in Excel. Make sure you are using actual
date values. Then all you need to do is use

=IF(AND($D$1>=A2,$D$1<=B2),C2/(B2-A2),"")

You need to consider the implications of what happens when A2 or B2
are identically equal, and adjust the >= and <= accordingly.

HTH,
Bernie
MS Excel MVP

Hello Experts,
Row 1 is formatted at mmm-yy. Columns A & B are formatted also

as
mmm-yy.

How do you write a formula for cell [D2] that looks at D1's date

to
see if

it is within the Column A2 and B2's date range. If true, divide

C2
by

(B2-A2). If false, then leave blank.

So far, my formula is
=IF(MONTH(D$5)>=MONTH($A2),ROUND($F7/MONTH($D7-$C7),2),"")
The problem is that this formula doesn't take into account of

the
change in

year 2004...cause a month comparison error too.
Furthermore, how do you re-write this to not exceed the B2's end date?

I've tried

=if((and(year(D$1),month(D$1))>=(and(year($A1),month($A1))),True,False
)

but that doesn't work either. It's giving me True results in

all
cells.

A B C D E F
G H I
1 Start End Total Oct-03 Nov-03 Dec-03 Jan-04 Feb-04
Mar-04
2 Nov-03 Feb-04 $100 $0 $25 $25 $25 $25
$0

Thanks in advance,
Ricky