If/Then using a due date for a bill

[redkahula]

I've been pulling my hair out all day... so I figured I should finally break down and ask someone.

I keep a spreadsheet for my bills (who doesn't, I know). On it, I've always either copy/pasted the "Amount" in the applicable cell for when a bill is due, or a cell reference (in case it changes, makes it easy). Lately, I've been thinking about making it more "automagical", and getting Excel to do that work for me.

So, I tried to insert an If/Then formula stating that "If the Date Due is after the Pay Day then the cell value should equal the Amount due... otherwise "".

The trouble I've run into is that while my car payment is due on the 5th there are precious few cells that will work. April 4th will work, for instance, because 4 is less then 5, however 11/29 won't work since 29>5 even though 12/13 is beyond the due date for my car payment.

Does this make sense?

I've tried all that I can imagine for IF/THENs and AND/OR combinations, but I'm stuck.

Help!

[TABLE="class: grid, width: 1229, align: center"]
<tbody>[TR]
[TD]Pay Date[/TD]
[TD]11/29/2013[/TD]
[TD]12/13/2013[/TD]
[TD]12/27/2013[/TD]
[TD]1/10/2014[/TD]
[TD]1/24/2014[/TD]
[TD]2/7/2014[/TD]
[TD]2/21/2014[/TD]
[TD]3/7/2014[/TD]
[TD]3/21/2014[/TD]
[TD]4/4/2014[/TD]
[TD]4/18/2014[/TD]
[TD]5/2/2014[/TD]
[TD]5/16/2014[/TD]
[TD]5/30/2014[/TD]
[TD]6/13/2014[/TD]
[TD]6/27/2014[/TD]
[TD]7/11/2014[/TD]
[TD]7/25/2014[/TD]
[/TR]
[TR]
[TD="align: center"]Pay Day[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD]Car Loan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]Balance[/TD]
[TD]Amount[/TD]
[TD]Date Due[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car Loan[/TD]
[TD]$6,019.68[/TD]
[TD]$134.28[/TD]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Consider something like this where Pay Day has the full date in it but the cell is custom formatted as "dd". Now 11/29/2013 is earlier than say 4/5/2014. In other words if Due Date is a full date (mm/dd/yyyy), you need to compare it to a full Pay Date not just the day of the month of the Pay date.

Excel Workbook
	A	B
1	Pay Date	11/29/2013
2	Pay Day	29
Sheet1

 

[vogel997]

This should solve the problem

=IF(OR((DATE(YEAR(B1),MONTH(B1),5)-B1)<-18,(DATE(YEAR(B1),MONTH(B1),5)-B1)>=0),$C$5,"")


[TABLE="width: 260"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Pay Date[/TD]
[TD="align: right"]11/29/13[/TD]
[TD="align: right"]12/13/13[/TD]
[TD="align: right"]12/27/13[/TD]
[/TR]
[TR]
[TD]Pay Day[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]Car Loan[/TD]
[TD="align: right"]134.28[/TD]
[TD][/TD]
[TD="align: right"]134.28[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]Balance[/TD]
[TD]Amount[/TD]
[TD]Date Due[/TD]
[/TR]
[TR]
[TD]Car Loan[/TD]
[TD="align: right"]$6,019.68[/TD]
[TD="align: right"]$134.28[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]