Excel Question

reciecup2000

New Member
Joined
Dec 16, 2005
Messages
1
I am trying to come up with a if then formula for a pay calcalculation based on the date.

We have a list with bills that show the due date only so it will only say 6 if its due on the 6th of the month.

The pay schedule however is on a rolling 2 week schedule. I have pulled out the date of the week to do a formula that says if the due date is between the dates then put the amount from another column. However that doesnt work when the month changes and the next date is less then the previous date.

How do I fix that.


1 People's Choice 724
1 Homeq 567
1 Alvin's 57
7 Aspire 120



30 13
Due Date Bill 12/30/2005 Due Date Bill 1/13/2006




Code:
iF(OR($A8=N$6,AND($A8>N$6,$A8<R$6)),$B8,"")

Edited by Von Pookie ~ fixed formula display
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

simselk

Active Member
Joined
Mar 14, 2005
Messages
279
Welcome Aboard!

Is this what you are trying to do:<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
MrExcel.xls
ABCD
1LastPayNextPay
212/30/20051/13/2006
3
4InvoiceDateBillDueDateAmount
55/11/2005Leasing12/6/2005$120
64/11/2005Other11/6/2005$500
710/12/2006Water2/6/2006$189
810/12/2006Paper1/6/2006$100
910/14/2006Wages1/6/2006$50
1010/18/2006Energy3/6/2006$500
1110/12/2006Food2/6/2006$800
1210/12/2006Rent1/6/2006$40
13
14$190
Sheet1
</SPAN><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003</FONT><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 15th May 2003</FONT></HR></BODY></HTML>
 

simselk

Active Member
Joined
Mar 14, 2005
Messages
279
MrExcel.xls
ABCD
1Last PayNext Pay
212/30/20051/13/2006
3
4Invoice DateBillDue DateAmount
55/11/2005Leasing12/6/2005$120
64/11/2005Other11/6/2005$500
710/12/2006Water2/6/2006$189
810/12/2006Paper1/6/2006$100
910/14/2006Wages1/6/2006$50
1010/18/2006Energy3/6/2006$500
1110/12/2006Food2/6/2006$800
1210/12/2006Rent1/6/2006$40
13
14$190
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,525
Messages
5,572,642
Members
412,478
Latest member
MakeItWorkVBA
Top