Is month between two dates?

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
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]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top