Bill reminder in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Automate > Bill reminder in excel

← Previous post -

Filed in Automate, Excel, Finance on Apr.19, 2011. Email This article to a Friend

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:

=IFERROR(INDEX(Bills!$B$3:$B$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(A1)), 1), "")

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.

Array formula in cell C1:

=IFERROR(DAY(INDEX(Bills!$C$3:$C$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(B1)), 1)), "") + CTRL + SHIFT + ENTER

Array formula in cell D1:

=IFERROR(INDEX(Bills!$D$3:$D$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(C1)), 1), "") + CTRL + SHIFT + ENTER

Explaining array formula in cell B1

To be continued...

Download excel sample file for this tutorial.

Bill reminder
(Excel 2007 Workbook *.xlsx)

Cash drawer bill extractor template in excel

Recommended categories

Comments (10)

10 Responses to “Bill reminder in excel”

Keith says:
March 28, 2012 at 12:48 am
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.
Reply
Oscar says:
March 28, 2012 at 9:08 am
Keith,
Is this what you are looking?
Formula in cell F2:
=SUM(D7:D33)
Reply
S RANGAMANNAR says:
April 7, 2012 at 10:38 am
yes , similar to what is narrated above.
Reply
David says:
June 2, 2012 at 9:51 pm
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?
Reply
Oscar says:
June 6, 2012 at 9:55 am
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.
Reply
Thomas says:
January 7, 2013 at 10:29 pm
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?
Reply
- Oscar says:
  January 9, 2013 at 2:30 pm
  Thomas,
  If I remember this correctly, the frequency is important. The array formula uses the frequency to calculate what months the bill is displayed.
  Reply
Tony says:
January 19, 2013 at 8:51 am
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?
Reply
Kevin says:
February 5, 2013 at 1:50 pm
how come it does not work for me? In reminder tab, it keep saying "name" Please help
Reply
- Oscar says:
  February 6, 2013 at 8:51 am
  Kevin,
  The IFERROR function does not work in excel 2003.
  Reply