How To Create A Recurring Monthly Schedule In Excel

Author: Oscar Cronquist Article last updated on September 20, 2019

Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to easily add/delete records without changing the formulas, in other words cell refs to the table are dynamic.

plot-recurring-expenses-on-worksheet1

I am going to use this data set and create a calendar with each expense at the correct date (and recurring dates).

plot-recurring-expenses-on-worksheet

The data above is also dynamic, if you change the year in cell D2 formulas in cell range B5:M35 are recalculated.

Array formula in B5:

=IF(MONTH(DATE($D$2, MONTH(B$4), $A5))=MONTH(B$4), SUM(IF(DATE($D$2, MONTH(B$4), $A5)=EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring n-th month])), TRANSPOSE(Table1[Amount]), "")), 0)

How to create an array formula

Type the formula in the formula bar
Press and hold CTRL + SHIFT
Press Enter once

If you made it right the formula is now surrounded by curly brackets, like this: {=array_formula} in the formula bar.

Copy formula

Copy cell B5 and paste it down to cell B35.

Copy cell range B5:B35 and paste it to the right all the way to M5:M35.

Formula in B4:

=DATE($D$2, COLUMN(A1), 1) + ENTER.

Copy cell B4 and paste it right to cell M4.

Formula in B36:

=SUM(B5:B35) + ENTER.

Copy cell B36 and paste it to the right to cell M36.

Explaining array formula in cell B5

Step 1 - Calculate date in cell B5

DATE($D$2,MONTH(B$4),$A5)

becomes

DATE(2010,1,1)

and returns 2010-1-1 (1/1/2010)

Step 2 - Build an array of recurring dates

EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring
n-th month]))

I can't show all dates here, there are two many. 1000 recurring dates for each date. I have to simplify, I am now using three recurring dates moving forward.

EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$3)-1)*TRANSPOSE(Table1[Recurring
n-th month]))

becomes

EDATE({40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205}, ({0;1;2})*{1, 3, 1, 2, 1, 1, 1, 6})

returns

{40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}

Step 3 - Check if current date is equal to any of the recurring dates and return the corresponding amount

IF(DATE($D$2, MONTH(B$4), $A5)=EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring
n-th month])), TRANSPOSE(Table1[Amount]), "")

becomes

IF(40179={40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}, TRANSPOSE(Amount), "")

becomes

IF(40179={40209, 40192, 40194, 40193, 40204, 40204, 40203, 40205;40237, 40282, 40225, 40252, 40235, 40235, 40234, 40386;40268, 40373, 40253, 40313, 40263, 40263, 40262, 40570}, {1000, 100, 500, 30, 50, 50, 100, 500}, "")

returns

{"","","","","","","","";"","","","","","","","";"","","","","","","",""}

Step 4 - Sum values

SUM(IF(DATE($D$2, MONTH(B$4), $A5)=EDATE(TRANSPOSE(Table1[Date]), (ROW($1:$1000)-1)*TRANSPOSE(Table1[Recurring
n-th month])), TRANSPOSE(Table1[Amount]), ""))

becomes

SUM({"","","","","","","","";"","","","","","","","";"","","","","","","",""})

and returns 0 (zero) in cell B5.