# Change quarters with date criteria

#### nico2pacnwa

##### New Member
Hi good people,

I am creating a monthly financial model from Jan-17 to Dec-19. I need help with the rent calculation. In my input sheet I have two inputs for rent 1) Start Date 2) Amount (annual figure) which is paid quarterly. What ever my start date is I want my rent to start on that date and previous months remain zero; there after I want my rent to appear after every 3 months.

Example: If my rent Start Date = "Apr-17" and Amount = "3000"; then my rent will appear as "750" (Quarterly Payments) in Apr-17, Jul-17, Oct-17 and Jan-18.

Hope you could help me with this.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1StartRentJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18
2Apr-173000750750750750750750750
3Jan-172500625625625625625625625625
4May-173200800800800800800800800

</tbody>
Sheet2

Worksheet Formulas
CellFormula
C2=IF((\$C\$1:C\$1)<\$A2,"",IF(MOD(COLUMNS(\$C2:C2)-MATCH(\$A2,\$C\$1:\$Z\$1,0),3)=0,\$B2/4,""))

</tbody>

<tbody>
</tbody>

Last edited:
Thank you AliGW that was very helpful.

You're welcome!

Replies
9
Views
152
Replies
3
Views
101
Replies
7
Views
475
Replies
4
Views
402
Replies
7
Views
143

1,196,081
Messages
6,013,318
Members
441,760
Latest member
Sharina

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