return a value in the week range containing the 20th of each month - except where the 20th falls on a weeked...

A

andrew.booth18

New Member
Joined
Jul 17, 2010
Messages
2
Hi everyone,

newbie here so please be gentle! :)

I'm trying to create a formula which will be used in a cash forecast spreadsheet.

Basically, i have a list of payments down column A in cells A2:A11, and across the top row starting from cell B1 is the date of the first day of that week, for example:

<TABLE style="WIDTH: 530pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=707 x:str><COLGROUP><COL style="WIDTH: 147pt; mso-width-source: userset; mso-width-alt: 7168" width=196><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" span=6 width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 147pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl24 height=17 width=196>WEEK BEGINNING</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 59pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=79 x:num="40357">28/06/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40364">05/07/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40371">12/07/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40378">19/07/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40385">26/07/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40392">02/08/2010</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: silver; WIDTH: 54pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl25 width=72 x:num="40399">09/08/2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl26 height=17>Rent</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="650">£ 650.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="650">£ 650.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl26 height=17>Insurance</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="25">£ 25.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="25">£ 25.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl26 height=17>Tax</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="30">£ 30.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="30">£ 30.00 </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl30 x:num="0">£ - </TD></TR></TBODY></TABLE>

I have successfully created a formula in the Tax row cells so that £30 is shown if the week beginning on the above date contains the last working day of the month, i did this by using:


=IF(AND(DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))


However, rent is always paid on the 5th of every month, except when this falls on a weekend, in which case it is deducted on the nearest working day before the 5th.

How can i change the formula to reflect this?

I tried:


=IF(AND(DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))<C1),650,0)< p>
which nearly worked apart from in September, where the the nearest working day to the 5th is the 3rd. This date falls in the week commencing 30.08.10, however the formula calculates that the date rent would be paid in that month is the 5th of August, which is correct i suppose, but it doesn't calculate that the 5th of the following month (September) actually falls in this week - then the next week on my sheet starts w/c 06.09.10 which is obviously after the 03.09.10 and so doesn't show any rent payments either, in effect 'skipping' this payment.

Is there a way for excel to use the w/c date on my sheet, and calculate that the 5th of September falls in the week commending the 30.08.10 and so input £30?

I don't think i'm a million miles away I just think i need some help in fine tuning, but if anyone has a better formula i'd be eternally grateful!

Thanks in advance,

Andy!
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
I'd go about doing this -- this way (note inclusion of 3 new columns - which can hide)
Just a thought... (FWIW)
Jim
Excel Workbook
ABCDEFGHI
1Current
2My CurrentTotals (Below)300.00--100.00-
3Month is:
47/31/2010*** YOU COULD HIDE THESE COLUMNS ***
5Proper
6Due DayDate ColumnAmt7/5/20107/12/20107/19/20107/26/20108/2/2010
7Rent7/5/20107/5/2010300.00300.00
8Taxes7/31/20107/26/2010100.00100.00
Sheet1
Excel 2007
Cell Formulas
RangeFormula
E2=SUM(E7:E18)
E7=IF($C7=E$6,$D7,"")
E8=IF($C8=E$6,$D8,"")
F2=SUM(F7:F18)
F7=IF($C7=F$6,$D7,"")
F8=IF($C8=F$6,$D8,"")
G2=SUM(G7:G18)
G6=F6+7
G7=IF($C7=G$6,$D7,"")
G8=IF($C8=G$6,$D8,"")
H2=SUM(H7:H18)
H6=G6+7
H7=IF($C7=H$6,$D7,"")
H8=IF($C8=H$6,$D8,"")
I2=SUM(I7:I18)
I6=H6+7
I7=IF($C7=I$6,$D7,"")
I8=IF($C8=I$6,$D8,"")
B7=DATE(YEAR(A4),MONTH(A4),5)
B8=EOMONTH(A4,0)
C7=IF(WEEKDAY(B7,2)<=5,B7,IF(WEEKDAY(B7,2)=6,B7-1,B7-2))
C8=INDEX($E$6:$I$6,,MATCH(B8,$E$6:$I$6,1))
 
Upvote 0
Woops,,,,,,,
Once again - I posted things "too soon" - there are problems with the above.
I need to continue trying to correct;

I'll try and post back (when corrected)..

Jim
 
Upvote 0
Welcome to the Board, Andy,

Data in Range A1:H4:

<TABLE style="WIDTH: 463pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=620><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=7 width=71><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=123>WEEK BEGINNING


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40357">28/06/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40364">05/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40371">12/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40378">19/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40385">26/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40392">02/08/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40399">09/08/2010</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Rent</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="650">£650.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="650">£650.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Insurance</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="25">£25.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="25">£25.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Tax</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="30">£30.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="30">£30.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00


</TD></TR></TBODY></TABLE>

Formula in B2 and copied across:

=IF(AND(DAY(B1+6) > =5,DAY(B1+6) < =11),650,0)

Formula in B3 and copied across:

=IF(AND(DAY(B1+6) > =5,DAY(B1+6) < =11),25,0)

Formula in B4 and copied across:

=IF(B1+4 > =WORKDAY(EOMONTH(B1,0)+1,-1),30,0)

Do these do as desired?

Matty
 
Upvote 0
Matty said:
Welcome to the Board, Andy,

Data in Range A1:H4:

<TABLE style="WIDTH: 463pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=620><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=7 width=71><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=123>WEEK BEGINNING



</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40357">28/06/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40364">05/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40371">12/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40378">19/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40385">26/07/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40392">02/08/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=71 align=right u1:num="40399">09/08/2010</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Rent</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="650">£650.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="650">£650.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Insurance</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="25">£25.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="25">£25.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71 align=right u1:num="0">£0.00</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=123>Tax</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="30">£30.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="30">£30.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 53pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71 align=right u1:num="0">£0.00



</TD></TR></TBODY></TABLE>

Formula in B2 and copied across:

=IF(AND(DAY(B1+6) > =5,DAY(B1+6) < =11),650,0)

Formula in B3 and copied across:

=IF(AND(DAY(B1+6) > =5,DAY(B1+6) < =11),25,0)

Formula in B4 and copied across:

=IF(B1+4 > =WORKDAY(EOMONTH(B1,0)+1,-1),30,0)

Do these do as desired?

Matty
Click to expand...

They do indeed!!!

Thank you enourmously!!!!!!

Seems such a simple formula too - i think i was that bogged down trying to get the working day thing that i made it much more harder than it had to be!!

Thank you so much - i could have been sat here for days otherwise and probably just got more and more complicated!!

things like this are why the internet is the best creation EVER!!! - always someone out there with an answer or willing to help out his fellow man (or woman!)

Much appreciated Matty!!

Andy
 
Upvote 0
OK, this (Maybe) is better........ Note the formula in Column E is different than
the formulas in Columns F thru I...
Good Luck
Jim
Excel Workbook
ABCDEFGHI
1INPUT CELLSCurrent
2My CurrentTotals (Below)300.00--100.00-
3Month is:
47/31/2010*** YOU COULD HIDE THESE COLUMNS ***
5Proper
6Due DayDate ColumnAmt7/5/20107/12/20107/19/20107/26/20108/2/2010
7Rent7/5/20107/5/2010300.00300.00
8Taxes7/31/20107/26/2010100.00100.00
Sheet1
Excel 2007
Cell Formulas
RangeFormula
E2=SUM(E7:E18)
E6=DATE(YEAR(A4),MONTH(A4),1)+7-WEEKDAY(DATE(YEAR(A4),MONTH(A4),1)-2)
E7=IF($C7<=E$6,$D7,"")
E8=IF($C8<=E$6,$D8,"")
F2=SUM(F7:F18)
F6=E6+7
F7=IF(AND($C7>E$6,$C7<=F$6),$D7,"")
F8=IF(AND($C8>E$6,$C8<=F$6),$D8,"")
G2=SUM(G7:G18)
G6=F6+7
G7=IF(AND($C7>F$6,$C7<=G$6),$D7,"")
G8=IF(AND($C8>F$6,$C8<=G$6),$D8,"")
H2=SUM(H7:H18)
H6=G6+7
H7=IF(AND($C7>G$6,$C7<=H$6),$D7,"")
H8=IF(AND($C8>G$6,$C8<=H$6),$D8,"")
I2=SUM(I7:I18)
I6=H6+7
I7=IF(AND($C7>H$6,$C7<=I$6),$D7,"")
I8=IF(AND($C8>H$6,$C8<=I$6),$D8,"")
B7=DATE(YEAR(A4),MONTH(A4),5)
B8=EOMONTH(A4,0)
C7=IF(WEEKDAY(B7,2)<=5,B7,IF(WEEKDAY(B7,2)=6,B7-1,B7-2))
C8=VLOOKUP(B8,$E$6:$I$6,MATCH(B8,$E$6:$I$6))
 
Upvote 0
They do indeed!!!

Thank you enourmously!!!!!!
Click to expand...

You're welcome.

i think i was that bogged down trying to get the working day thing
Click to expand...

It happens all too often to me as well. I've found with Excel that the more I learn about it the more likely I am to overlook the obvious!

Matty
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
817
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
583
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A

Forum statistics

Threads
1,214,642
Messages
6,120,701
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top