Amortization Schedule-Need help with Dates

  • Thread starter Thread starter Ricky
  • Start date Start date
R

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
 
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
 
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


Bernie Deitrick said:
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 said:
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
Click to expand...
Click to expand...
 
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

Ricky said:
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


Bernie Deitrick said:
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 said:
Hello Experts,
Row 1 is formatted at mmm-yy. Columns A & B are formatted also
Click to expand...
as
mmm-yy.
How do you write a formula for cell [D2] that looks at D1's date
Click to expand...
to
see if
it is within the Column A2 and B2's date range. If true, divide
Click to expand...
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
Click to expand...
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
Click to expand...
=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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top