# Formula to work out if event is due in month

#### spicersr

##### New Member
I am sure there is an easy solution to this, that I just can't see. I have many payments due quarterly, each with differing start months. For a given month, I need to know if a payment will be due.

For example, using the table below, the £100 in the first row is due in Jan, Apr, Jul, Oct the £200 in second row due in Feb, May, Aug, Nov, etc.

I want a formula to tell me what is due in October. Any ideas? I've tried looking at VBA with something like [Month(StartDate) + Freq * i] and looping through, but I feel there should be an easier way.

 Start Date Frequency £ amount 01/01/2016 3 100 01/02/2016 3 200 01/03/2016 6 50 01/04/2016 4 20

<tbody>
</tbody>

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Just to clarify, the Frequency is the number of months between payment, they are not all due quarterly!

Hi,
In case you're using Excel from 2007 onwards you can use the formula =EOMONTH(start date, months)
This will give you the end of the month you want, then you can deduct any days you want by adding at the end -nr days
i.e. =EOMOTH(A2,B2)-5 in case you have your table above starting at A1.
Don't forget to format cells to date.

Put a date in cell E1 eg

01/10/2016

Then this formula should work ok:

=IF(MOD(DATEDIF(A2,\$E\$1,"m")/B2,1)=0,"Due","Not Due")

Replies
3
Views
105
Replies
3
Views
324
Replies
0
Views
370
Replies
6
Views
115
Replies
5
Views
247

1,196,512
Messages
6,015,628
Members
441,912
Latest member
Rayna_rahman00

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

### Which adblocker are you using?

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

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