Formula to confirm # of payments in a quarter

[bcarroll76]

Hi all, I am in need of a formula that will confirm how many months fall within each quarter based off of the Term Begin Date noted in Column A and multiply that by the dollar amount in column B. End result is to know how much is to be paid in each quarter.

I suspect it may need to be two different functions (one to find the # of months and one to multiply that by the dollar amount, and I would be Ok with that)


Thanks All!

[6StringJazzer]

Note:

2/1/2023 is not in any of the quarters shown
9/31/2023 is not a valid date (for any year)
8/1/2024 is not in any of the quarters shown

This formula is for starting in row 5, copy down. It will not detect the the first error above.

Please Login or Register  to view this content.

[bcarroll76]

sorry, yea I forgot to make the changes to the sept last day of the month in the new spreadsheet.

Even though 2/1/2023 does not appear in the quarters listed, this is considered the start date of the term which lasts for 5yrs. So in quarter one I need 3 months worth of costs show in that cell. For 8/1/24, since it does not fall in one of those quarters, I would need it to show 0 for each quarter.

[JeteMc]

Try pasting the following formula into cell D5 and then dragging the fill handle over and down:

Please Login or Register  to view this content.

Note that cell D3 needs to be changed to read 9/30/2023.
Let us know if you have any questions.

[Phuocam]

Or try:

=IF($A5>D$3,0,DATEDIF(MAX($A5,D$2),D$3+1,"m")*$B5)

[bcarroll76]

Thank you JeteMc and Phuocam, both options work great!

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

[JeteMc]

You're Welcome and thank you for the feedback. As stated above, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.