Formula to work out if event is due in month

spicersr

New Member
Joined
Jun 6, 2016
Messages
3
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 DateFrequency£ amount
01/01/20163100
01/02/20163200
01/03/2016650
01/04/2016420

<tbody>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Just to clarify, the Frequency is the number of months between payment, they are not all due quarterly!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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