Bill reminder in excel
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where you enter your bills due date and their frequency.
However what makes mine different than yours is I'd love for it to auto populate the bills based on my pay periods. I'd want it to list the bills I have to pay with the corresponding check.
In other words I dont care what date my phone bill is due, because I live hand to mouth, so their due date is actually the day I get paid, make sense?
If I get paid on the 5th and then again on the 19th, the sheet should list all the bills I have between the 5th and 19th as bills I need to pay with my check on the 5th. if I have a bill due on the 18th it should still list it as to be paid from the 5th check because paying with my check on the 19th would be too late. make sense?
Answer:
Sheet Bills
Sheet Reminder
Array formula in cell B1 sheet Reminder:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to create an Excel defined Table
I am using an Excel defined Table because a cell reference to an Excel defined table adjusts automatically if you add or remove records.
- Select a cell in the data set sheet Reminder.
- Press CTRL + T.
- Press with left mouse button on checkbox if your data set has headers.
- Press with left mouse button on OK button.
Explaining array formula in cell B7
The remaining array formulas in cell C7 and D7 are similar to the one in cell B7, they extract a different part of the Excel defined Table.
Step 1
The MONTH function returns a number representing a month based on an Excel date. 1= Jan, 2 = Feb, 3 = March ... 12 = Dec.
MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])
becomes
4=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])
becomes
4={1;1;1;1;1;1;1}+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])
becomes
4={1;1;1;1;1;1;1}+({1,2,3,4,5,6,7,8,9,10,11,12}-1)*Table1[Frequency (months)])
becomes
4={1;1;1;1;1;1;1}+({0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11})*Table1[Frequency (months)])
becomes
4={1;1;1;1;1;1;1}+({0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11})*{1;3;1;6;12;12;6})
becomes
4={1;1;1;1;1;1;1}+{0, 1, 2, ... , 66}
becomes
4={1, 2,... , 67}
and returns
{FALSE, ... , FALSE}
Step 2
The DAY function returns the day of an Excel date.
(DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)
becomes
(19<5)*({15;7;25;14;24;10;16}>=19)
becomes
FALSE*{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}
and returns {0; 0; 0; 0; 0; 0; 0}
Step 3
(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)
becomes
(19>=5)*(19<19)*({15;7;25;14;24;10;16}>=5)*({15;7;25;14;24;10;16}<19)
becomes
TRUE*FALSE*{1;1;0;1;0;1;1}
and returns
{0; 0; 0; 0; 0; 0; 0}
Step 4
(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)
becomes
(19>=19)*({15;7;25;14;24;10;16}>=19)
becomes
TRUE*{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}
and returns
{0; 0; 1; 0; 1; 0; 0}
Step 5 - Add/Multiply arrays
AND logic - Multiply arrays
OR logic - Add arrays
(MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4))
becomes
{FALSE,... , FALSE}*({0; 0; 0; 0; 0; 0; 0}+{0; 0; 0; 0; 0; 0; 0}+{0; 0; 1; 0; 1; 0; 0})
becomes
{FALSE, ... , FALSE}*{0;0;1;0;1;0;0}
and returns
{0, ... , 1, ... , 0}
Step 6 - Convert TRUE (1) to corresponding row number
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, "")
returns
{"", ... , 3, ... , ""}
Step 7 - Extract k-th smallest row number in array
The SMALL function returns the k-th smallest value in a cell range or array. SMALL( array, k)
SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1))
becomes
SMALL({"", ... , 3, ... , ""}, ROW(A1))
becomes
SMALL({"", ... , 3, ..., ""}, 1)
and returns 3.
Step 8 - Return value
The INDEX function returns a value based on a row and column number.
INDEX(Table1[Bill], SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1)), 1)
returns "Electrical" in cell B7.
Step 9 - Avoid errors
The IFERROR function returns a blank if formula returns an error.
Array formula in cell C1:
Array formula in cell D1:
Finance category
Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]
Table of Contents Compare the performance of your stock portfolio to S&P 500 Tracking a stock portfolio in Excel (auto […]
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
Excel categories
15 Responses to “Bill reminder in excel”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
This is really helpful!
Can you explain how to modify it to display a running total of bills due by date? What I'm envisioning doing is plotting that as my projected bank balance (including sources of income as "negative bills").
Your other tutorials explain how to handle the plotting, but I'm not sure how to generate the running total itself.
Keith,
Is this what you are looking?
Formula in cell F2:
yes , similar to what is narrated above.
This xls worksheet is almost what i am looking for. I am looking to have a drop down list of pay days, and have it auto populate the bill due from that paycheck if the bill due day, which is always the same number each month falls within two paydays. For example, If a bill due date falls during two dates (paydays) then the bill would need to come out of the earlier paycheck. Does that make since?
David,
I made a version with drop down lists:
Bill-reminder-version2.xlsx
The current date (today) decides which bills are displayed. Try changing the date in cell C2, sheet Reminder.
this is a very nice worksheet, but I was wondering, just what does the drop down on the bills page have to do with anything in the formulas, or is it just so that you/me, know that it is a monthly, bimonthly quarterly... bill?
Thomas,
If I remember this correctly, the frequency is important. The array formula uses the frequency to calculate what months the bill is displayed.
Great worksheet!
Question: I have a bill that must be paid every two weeks. How would I be able to modify the frequency in Bills worksheet?
how come it does not work for me? In reminder tab, it keep saying "name" Please help
Kevin,
The IFERROR function does not work in excel 2003.
Is there anyway to import the payment due dates into a printable calendar? I have seen other templates with perpetual calendars that can be populated from a list simular to your template but yours is the only one I have found so far that put bills in like I would like.
Bill
Is there any way to make this formula not dependent on the current month or current day? For example, my bills don't change from month to month but my pay dates do. I just want to populate the list of bills I have to pay with each check without having to enter the month or have it rely on the 'TODAY' function. I was trying to see if there was a way to list the bills just by the range of the pay dates but I couldn't figure it out. I'd be really interested to know.
does this work within MS office 2016?
Can you help me. I get paid weekly, what do I chang the formula too.
It doesnt go to the next month. Will only retun dates wih in the month.