Formula to forecast bill payment dates based on bi-weekly pay-checks.

[helmerr]

Hi,

I am working on a spreadsheet (attached) that will automatically add the bill amount due for the appropriate (rows) to the appropriate (column) of the bi-weekly pay-date.

I have the formulas added already to calculate the bi-weekly paydate.
I also started adding formulas to insert the bill amount in the correct column, but it doesn't work for bills due between the end of month to beginning for obv reasons. I'm sure there is a better formula to use.

Most bills are due once a month. I do have a few that are due bi-weekly as shown on the attached. Thanks for your help.

Here is a link to my excel in dropbox https://www.dropbox.com/s/5ah32ukgpblrewt/forecast.xlsx?dl=0

 

[skywriter]

You could try this.
Copy the formula in F6 down and then across.

Excel 2010

	A	B	C	D	E	F	G	H	I
Rent
TWC
AES

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"](1,002)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"](1,002)[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](1,002)[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #E1F3D6"]Sebring[/TD]
[TD="bgcolor: #E1F3D6, align: right"]1[/TD]
[TD="bgcolor: #E1F3D6, align: right"](320)[/TD]
[TD="bgcolor: #E1F3D6, align: right"][/TD]
[TD="bgcolor: #E1F3D6, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"](320)[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](320)[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"](55)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](55)[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](55)[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #E1F3D6"]GSWSA[/TD]
[TD="bgcolor: #E1F3D6, align: right"]10[/TD]
[TD="bgcolor: #E1F3D6, align: right"](46)[/TD]
[TD="bgcolor: #E1F3D6, align: right"][/TD]
[TD="bgcolor: #E1F3D6, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](46)[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](46)[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"](320)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](320)[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0 [/TD]
[TD="bgcolor: #FFFFFF, align: right"](320)[/TD]

</tbody>

Forecast​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]=IF(MONTH(F$2)=MONTH(G$2),IF(AND($B6>=F$3,$B6<G$3),$C6,0),IF(OR($B6>=F$3,$B6<G$3),$C6,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=IF(MONTH(G$2)=MONTH(H$2),IF(AND($B6>=G$3,$B6<H$3),$C6,0),IF(OR($B6>=G$3,$B6<H$3),$C6,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]=IF(MONTH(H$2)=MONTH(I$2),IF(AND($B6>=H$3,$B6<I$3),$C6,0),IF(OR($B6>=H$3,$B6<I$3),$C6,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I6[/TH]
[TD="align: left"]=IF(MONTH(I$2)=MONTH(J$2),IF(AND($B6>=I$3,$B6<J$3),$C6,0),IF(OR($B6>=I$3,$B6<J$3),$C6,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[skywriter]

The formula didn't copy right.

`

Excel 2010
	A	B	C	D	E	F
5	income-Smith	1	350
6	Rent	1	(1,002)			(1,002)
7	Sebring	1	(320)			(320)
8	TWC	2	(55)			0
9	GSWSA	10	(46)			0
Forecast

Cell Formulas
Range	Formula
F6	=IF(MONTH(F$2)=MONTH(G$2),IF(AND($B6>=F$3,$B6),$C6,0),IF(OR($B6>=F$3,$B6),$C6,0))

`

 

[skywriter]

Use the formula in the post above. Post #3. Something really weird going on with the HTML maker I used in the first post it somehow changes the formula as you can see in the difference between the two posts.

 

[helmerr]

wow! pretty close! I need to make it the exact opposite though. I went through and manually highlighted in green where the payments should be made. Here is the newest link. Thanks for your help so far!!
https://www.dropbox.com/s/5ah32ukgpblrewt/forecast.xlsx?dl=0

 

[skywriter]

wow! pretty close! I need to make it the exact opposite though. I went through and manually highlighted in green where the payments should be made. Here is the newest link. Thanks for your help so far!!
https://www.dropbox.com/s/5ah32ukgpblrewt/forecast.xlsx?dl=0

Makes no sense to me. The formulas pay on the dates you showed in your original spreadsheet. It appeared as if for instance your bill is due on the first and your payday falls on the 2nd day of a month you wanted the bill paid from the previous payday, so now I don't know what you are doing.

That's not the same spreadsheet you had up earlier. The first column with 2014 in row 1 was F and it was Dec 19.

 

[skywriter]

Yeah you changed the spreadsheet which caused the cell references to be off.
Judging by most of your green shading I was right in my first assessment of what you wanted.
In the new spreadsheet I cannot help you with gas food hcpr since you don't have a due day for those, so those formulas won't work. You have cell F11 shaded green for electric but that's now where the formula works because I think you shaded the wrong cell. F11 is 2 days after your pay date so it should evaluate to the previous pay date which it does.

Excel 2010
	E
6	0
Forecast

Cell Formulas
Range	Formula
E6	=IF(MONTH(E$2)=MONTH(F$2),IF(AND($B6>=E$3,$B6),$C6,0),IF(OR($B6>=E$3,$B6),$C6,0))

 

[helmerr]

Hi. Yea you're right. I did mess up on F11. I copied over the last formula you gave me and looked over the spreadsheet. It looks perfect.
Here is the final. https://www.dropbox.com/s/5ah32ukgpblrewt/forecast.xlsx?dl=0 Didn't mean to confuse you, hope it makes sense now.
Thanks again!

 

[skywriter]

Hi. Yea you're right. I did mess up on F11. I copied over the last formula you gave me and looked over the spreadsheet. It looks perfect.
Here is the final. https://www.dropbox.com/s/5ah32ukgpblrewt/forecast.xlsx?dl=0 Didn't mean to confuse you, hope it makes sense now.
Thanks again!

My pleasure.

 

[megush]

WOW! I know this is a really old thread & I feel kind of dumb for not finding this before now but I have been wanting this exact worksheet but for a weekly pay schedule FOREVER & just never found quite the right one. This is perfect & was easily customized to my pay schedule & bill list. THANK YOU!!!