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

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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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))
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

andrew.booth18

New Member
Joined
Jul 17, 2010
Messages
2

ADVERTISEMENT

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

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
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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))
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
They do indeed!!!

Thank you enourmously!!!!!!

You're welcome.

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

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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,973
Members
416,953
Latest member
broexc

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
Top