Is month between two dates?

[Factotum]

I'm trying to build a payment schedule that shows the monthly payment on each month, but I'm having trouble with the conditions for populating the amount.

The table below shows what I'm trying to do - the values are shown in row two and the formulas in row three. The formula for total months works fine (unless someone has a better suggestion). It is intended to treat any partial month as a full month.

My problem is with the last two columns. How can I calculate, for example: IF(October is between Start and End Dates , Monthly Cost , 0). I anticipate each monthly payment column having the formula updated with that month: IF(Nov is between Start and End Date....etc.)

Unfortunately, I can't use VBA for this one, so I'm looking for formulas only. Thanks!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Cost[/TD]
[TD="align: center"]Total Months[/TD]
[TD]Monthly Cost[/TD]
[TD]October 2017 Payment[/TD]
[TD]November 2017 Payment[/TD]
[TD]December 2017 Payment[/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]3/15/18[/TD]
[TD]$500[/TD]
[TD="align: center"]5[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]Manual Input[/TD]
[TD]=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1[/TD]
[TD]=C1/D1[/TD]
[TD]???[/TD]
[TD]???[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

 

[cooper645]

could you not change your header to say;

10/1/17
11/1/17
12/1/17 etc

then simply format your header as custom: mmmm yyyy "Payment"

this will then display the date in your header as;
October 2017 Payment
November 2017 Payment
December 2017 Payment etc

But you would then be able to use the date value of your header cell to incorporate into a formula to test if it falls between start and end dates

so F2 would be
=IF(AND(F$1>=$A2,F$1<=$B2),"Monthly cost calc/ref here",0)

then drag across and down as needed.
G2:
=IF(AND(G$1>=$A2,G$1<=$B2),"Monthly cost calc/ref here",0)

H2:
=IF(AND(H$1>=$A2,H$1<=$B2),"Monthly cost calc/ref here",0)

 

[danl1988]

I didn't audit the formulas, but conceptually I agree with cooper. The challenge is that your header for the column isn't actually "October" or any date in October, but a text string with the word "October" in it. If you replace that with 10/1/17 (or similar) you'd be able to generate a fairly simple IF(AND) statement to determine if October falls within the start-end date range.

 

[Factotum]

Thank you dan and cooper! I almost had the IF(AND... logic worked out, but couldn't quite get it right. I didn't realize that an actual date could be formatted the way you suggested - that was the golden ticket. It works like a charm, thank you for much!

 

[Factotum]

This isn't a game changer, but just wanted to mention that the formula you suggested, cooper, only works if the Start Date is the first day of the month. For example, if my start date is 10/1/18 then it will calculate a payment in October. If the start date is anywhere between 10/2/18 and 10/31/18, then the first payment is calculated in November. Like I said, this is no big deal - I'll just instruct the user to always enter the first day of the month as the start date.

Thanks again!

 

[danl1988]

This isn't a game changer, but just wanted to mention that the formula you suggested, cooper, only works if the Start Date is the first day of the month. For example, if my start date is 10/1/18 then it will calculate a payment in October. If the start date is anywhere between 10/2/18 and 10/31/18, then the first payment is calculated in November. Like I said, this is no big deal - I'll just instruct the user to always enter the first day of the month as the start date.

Thanks again!

Hi Fatso,

That sounds a bit annoying as a user, and probably error-prone (someone's going to screw it up eventually). Another option would be to either (1) amend the formulas or (2) for a quick and dirty solution, insert a new column that takes the start date and converts it to the first date of the month no matter what. For example, if your "new start date" was in column B, you could make the formula in cell B2 "=EOMONTH(A2,-1)+1.

This basically says "change the date in cell A2 to the last day of the previous month, then add a day", effectively giving you the first day of the month. You'd then re-point the formulas to this new start date.

 

[Factotum]

Thanks for the suggestions Dan. I attempted to change my formulas but couldn't get it right. I'm sure I'm missing something obvious, but that's ok. The work-around that I ended up using was setting up Data Validation for the Start Date column. There's a note in the spreadsheet reminding the users to enter only the first day of the month, plus the Data Validation to throw an error if the user doesn't comply. This sheet is only going to be used by two people and I'm building it as a personal favor, so I don't think I'll worry too much about it. Now if this was for me on the other hand...I'd probably waste hours trying to get it perfect.