Macro help

Emmily · Mar 11, 2011

Hi i have work task which i'm hoping can be automated. The task is to track clients who are on Payment arrangements according to their payment frequency. Every Tuesday I will manually try and work out which clients i need to chase up for payment based on their frequency. I was hoping there is VBA procedure that can be wrtitten so when i run the code it will filter for those specific clients.

Below is sample data

Excel Workbook

A

B

C

D

E

F

G

H

1

Title

First Name

Last Name

Company Name

Total

Instalment

Payment Start Date

Frequency

2

Mr

Jamie

Smith

Test1

7,846.64

100.00

29/04/2009

Weekly

3

Mr

Christopher

Bloggs

Test2

5,830.00

50.00

06/04/2009

Weekly

4

Mrs

Brie

Masters

Test3

28,633.44

500.00

08/09/2010

Monthly

5

Mrs

Ben

Buckingham

Test4

800.00

200.00

22/03/2011

Monthly

6

Mr

Damien

Cot

Test5

4,400.00

440.00

22/03/2011

Monthly

7

Mr

Peter

Pesh

Test6

17,811.00

100.00

04/03/2011

Weekly

8

Mr

Neil

Smith

Test7

1,803.00

910.00

07/07/2011

Quarterly

9

Mr

Grant

Denyer

Test8

3,960.00

330.00

08/03/2011

Weekly

10

Mr

Steven

Gerrard

Test9

8,400.00

700.00

08/03/2011

Monthly

11

Mr

Guy

Seb

Test10

4,020.00

100.00

22/03/2011

Fortnightly

Sheet1

Emmily · Mar 12, 2011

shg4421 said:
Emmily, can you explain the exact logic you used in highlighting those rows based on those dates?

Ok the logic behind this task is for me to check on a weekly basis which of my clients have payed according to their payment frequency. So if we take the 05/04/11 as an example I would expect the code to filter for all the weekly payments since they are weekly occurence.

If we take the 15/04/11 again i would expect to see the weeklys plus i would expect to see the monthly which are 08 because i would have expected them to have payed on 08/04/11 and i would need to allow at least 1 day for the payment to go through so on 15/04/11 i would check this. For the Fortnightly the first payment is 22/03/11 and the second payment would be 05/04/11, i would expect the funds in the a/c by 06/04/11 so the following week being 15/04/11 I would check this since i will be checking once a week.

Hope this explains this let me know if you have any more questions.

shg · Mar 12, 2011

So ...

Weekly should always be highlighted

Fortnightly should be highlighted if the current date is ??? with respect to the date in col G.

Monthly should be highlighted if the current date is ??? with respect to the date in col G.

Quarterly if ???

Emmily · Mar 12, 2011

shg4421 said:
So ...

Weekly should always be highlighted

Fortnightly should be highlighted if the current date is ??? with respect to the date in col G.

Monthly should be highlighted if the current date is ??? with respect to the date in col G.

Quarterly if ???

Weekly should always be highlighted since they will be paying every week and i will be check this weekly.

Fortnighly should be Col G date +2 weeks is when i check, if you think about it on 22/03/11 was the first payment date, I would expect the funds to be in the a/c on 23/03/11, so on the 29/03/11 i would check this. The second payment date would be 05/04/11 i would expect the funds to be payed on 06/04/11, so on 15/04/11 i would check this.

Monthly rule would be same principle if the first payment is 08/03/11 I would check on 15/03/11, if the second payment is 08/04/11 i would check on 15/04/11

If quaterly then the first payment date is 07/07/11, so funds expected the next Business day, i would then check on 12/07/11 if the client has payed, so macro should filter when run on 12/07/11, the next payment date would be 07/10/11, so i would check on 11/10/11

Emmily · Mar 12, 2011

shg4421 said:
So ...

Weekly should always be highlighted

Fortnightly should be highlighted if the current date is ??? with respect to the date in col G.

Monthly should be highlighted if the current date is ??? with respect to the date in col G.

Quarterly if ???

You have to think that I would be checking the following week after the payment date in Col G with respect to their frequency.

shg · Mar 12, 2011

How about a little different approach?

<TABLE style="WIDTH: 467pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=624 border=0 x:str><COLGROUP><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 1170" width=26><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2925" width=64><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2998" width=66><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 2194" width=48><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2706" width=59><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2779" width=61><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 4096" width=90><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2742" width=60><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 4608" width=101><COL style="WIDTH: 37pt" width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 19pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: #909090" width=26 height=16> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=64> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 49pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=66> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 36pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=48> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 44pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=59> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 46pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=61> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 67pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=90> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 45pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=60> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=101>Check Date</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 37pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=49> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: #909090" height=16> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num="40709">Wed 15 Jun 2011</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 19pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: #f3f3f3" width=26 height=16>Title

</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=64>First Name</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=66>Last Name</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 36pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=48>Company</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=59>Total</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=61>Instalment</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=90>Start</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 45pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=60>Frequency</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=101>Last Pmt Due</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Jamie</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Smith</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Test1</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="7846.64">$ 7,846.64</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num>100</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="39932">Wed 29 Apr 2009</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Weekly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40709" x:fmla='=IF(G4>I$2, "",
CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G4 + 7 * INT((I$2 - G4)/7),
G4 + 14 * INT((I$2 - G4)/14),
EDATE(G4, DATEDIF(G4, I$2, "m")),
EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3))
))'>Wed 15 Jun 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I4<>"", I$2-I4 < 7)' x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Christopher</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Bloggs</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Test2</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="5830">$ 5,830.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num>50</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="39909">Mon 06 Apr 2009</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Weekly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40707" x:fmla='=IF(G5>I$2, "",
CHOOSE(MATCH(H5, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G5 + 7 * INT((I$2 - G5)/7),
G5 + 14 * INT((I$2 - G5)/14),
EDATE(G5, DATEDIF(G5, I$2, "m")),
EDATE(G5, 3 * INT(DATEDIF(G5, I$2, "m")/3))
))'>Mon 13 Jun 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I5<>"", I$2-I5 < 7)' x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mrs</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Brie</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Masters</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test3</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="28633.439999999999">$ 28,633.44</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>500</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40429">Wed 08 Sep 2010</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Monthly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40702" x:fmla='=IF(G6>I$2, "",
CHOOSE(MATCH(H6, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G6 + 7 * INT((I$2 - G6)/7),
G6 + 14 * INT((I$2 - G6)/14),
EDATE(G6, DATEDIF(G6, I$2, "m")),
EDATE(G6, 3 * INT(DATEDIF(G6, I$2, "m")/3))
))'>Wed 08 Jun 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I6<>"", I$2-I6 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mrs</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Ben</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Buckingham</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test4</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="800">$ 800.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>200</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40624">Tue 22 Mar 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Monthly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40685" x:fmla='=IF(G7>I$2, "",
CHOOSE(MATCH(H7, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G7 + 7 * INT((I$2 - G7)/7),
G7 + 14 * INT((I$2 - G7)/14),
EDATE(G7, DATEDIF(G7, I$2, "m")),
EDATE(G7, 3 * INT(DATEDIF(G7, I$2, "m")/3))
))'>Sun 22 May 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I7<>"", I$2-I7 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Damien</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Cot</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test5</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="4400">$ 4,400.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>440</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40624">Tue 22 Mar 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Monthly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40685" x:fmla='=IF(G8>I$2, "",
CHOOSE(MATCH(H8, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G8 + 7 * INT((I$2 - G8)/7),
G8 + 14 * INT((I$2 - G8)/14),
EDATE(G8, DATEDIF(G8, I$2, "m")),
EDATE(G8, 3 * INT(DATEDIF(G8, I$2, "m")/3))
))'>Sun 22 May 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I8<>"", I$2-I8 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Peter</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Pesh</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Test6</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="17811">$ 17,811.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num>100</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40606">Fri 04 Mar 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Weekly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40704" x:fmla='=IF(G9>I$2, "",
CHOOSE(MATCH(H9, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G9 + 7 * INT((I$2 - G9)/7),
G9 + 14 * INT((I$2 - G9)/14),
EDATE(G9, DATEDIF(G9, I$2, "m")),
EDATE(G9, 3 * INT(DATEDIF(G9, I$2, "m")/3))
))'>Fri 10 Jun 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I9<>"", I$2-I9 < 7)' x:bool="TRUE">TRUE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Neil</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Smith</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test7</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="1803">$ 1,803.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>910</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40731">Thu 07 Jul 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Quarterly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:str="" x:fmla='=IF(G10>I$2, "",
CHOOSE(MATCH(H10, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G10 + 7 * INT((I$2 - G10)/7),
G10 + 14 * INT((I$2 - G10)/14),
EDATE(G10, DATEDIF(G10, I$2, "m")),
EDATE(G10, 3 * INT(DATEDIF(G10, I$2, "m")/3))
))'> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I10<>"", I$2-I10 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Grant</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Denyer</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test8</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="3960">$ 3,960.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>330</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40758">Wed 03 Aug 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Weekly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:str="" x:fmla='=IF(G11>I$2, "",
CHOOSE(MATCH(H11, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G11 + 7 * INT((I$2 - G11)/7),
G11 + 14 * INT((I$2 - G11)/14),
EDATE(G11, DATEDIF(G11, I$2, "m")),
EDATE(G11, 3 * INT(DATEDIF(G11, I$2, "m")/3))
))'> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I11<>"", I$2-I11 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Steven</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Gerrard</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Test9</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="8400">$ 8,400.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num>700</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right x:num="40758">Wed 03 Aug 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">Monthly</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:str="" x:fmla='=IF(G12>I$2, "",
CHOOSE(MATCH(H12, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G12 + 7 * INT((I$2 - G12)/7),
G12 + 14 * INT((I$2 - G12)/14),
EDATE(G12, DATEDIF(G12, I$2, "m")),
EDATE(G12, 3 * INT(DATEDIF(G12, I$2, "m")/3))
))'> </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I12<>"", I$2-I12 < 7)' x:bool="FALSE">FALSE</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; mso-ignore: style; mso-pattern: auto none" height=16>Mr</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Guy</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Seb</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Test10</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="4020">$ 4,020.00</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num>100</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40624">Tue 22 Mar 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none">Fortnightly

</TD><TD class=xl33 id=td_post_2644189 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto none" align=right x:num="40708" x:fmla='=IF(G13>I$2, "",
CHOOSE(MATCH(H13, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G13 + 7 * INT((I$2 - G13)/7),
G13 + 14 * INT((I$2 - G13)/14),
EDATE(G13, DATEDIF(G13, I$2, "m")),
EDATE(G13, 3 * INT(DATEDIF(G13, I$2, "m")/3))
))'>Tue 14 Jun 2011</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=middle x:fmla='=IF(I13<>"", I$2-I13 < 7)' x:bool="TRUE">TRUE</TD></TR></TBODY></TABLE>

The formula in I4 and down computes the due date of the last payment on or prior to the check date:

=IF(G4>I$2, "",
CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G4 + 7 * INT((I$2 - G4)/7),
G4 + 14 * INT((I$2 - G4)/14),
EDATE(G4, DATEDIF(G4, I$2, "m")),
EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3))
))

The formula in the next column computes whether the row should be highlighted:

=IF(I4<>"", I$2-I4 < 7)

Emmily · Mar 12, 2011

I would have expected the red cells to be TRUE, if the payment date is 08 then on 15/03/11 i would be checking this.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

2

15/03/2011

3

Title

First Name

Last Name

Company Name

Total

Instalment

Payment Start Date

Frequency

Last Pmt Due

Check

4

Mr

Jamie

Smith

Test1

7,846.64

100

29/04/2009

Weekly

9-Mar-11

TRUE

5

Mr

Christopher

Bloggs

Test2

5,830.00

50

6/04/2009

Weekly

14-Mar-11

TRUE

6

Mrs

Brie

Masters

Test3

28,633.44

500

8/09/2010

Monthly

8-Mar-11

FALSE

7

Mrs

Ben

Buckingham

Test4

800

200

22/03/2011

Monthly

FALSE

8

Mr

Damien

Cot

Test5

4,400.00

440

22/03/2011

Monthly

FALSE

9

Mr

Peter

Pesh

Test6

17,811.00

100

4/03/2011

Weekly

11-Mar-11

TRUE

10

Mr

Neil

Smith

Test7

1,803.00

910

7/07/2011

Quarterly

FALSE

11

Mr

Grant

Denyer

Test8

3,960.00

330

8/03/2011

Weekly

15-Mar-11

TRUE

12

Mr

Steven

Gerrard

Test9

8,400.00

700

8/03/2011

Monthly

8-Mar-11

FALSE

13

Mr

Guy

Seb

Test10

4,020.00

100

22/03/2011

Fortnightly

FALSE

Sheet2

shg4421 said:
How about a little different approach?

<table style="width: 467pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="624"><colgroup><col style="width: 19pt;" width="26"><col style="width: 48pt;" width="64"><col style="width: 49pt;" width="66"><col style="width: 36pt;" width="48"><col style="width: 44pt;" width="59"><col style="width: 46pt;" width="61"><col style="width: 67pt;" width="90"><col style="width: 45pt;" width="60"><col style="width: 76pt;" width="101"><col style="width: 37pt;" width="49"></colgroup><tr style="height: 12pt;" height="16"><td style="border: medium none rgb(212, 208, 200); width: 19pt; height: 12pt; background-color: rgb(144, 144, 144);" height="16" width="26">
</td><td style="border: medium none rgb(212, 208, 200); width: 48pt; background-color: rgb(144, 144, 144);" width="64">
</td><td style="border: medium none rgb(212, 208, 200); width: 49pt; background-color: rgb(144, 144, 144);" width="66">
</td><td style="border: medium none rgb(212, 208, 200); width: 36pt; background-color: rgb(144, 144, 144);" width="48">
</td><td style="border: medium none rgb(212, 208, 200); width: 44pt; background-color: rgb(144, 144, 144);" width="59">
</td><td style="border: medium none rgb(212, 208, 200); width: 46pt; background-color: rgb(144, 144, 144);" width="61">
</td><td style="border: medium none rgb(212, 208, 200); width: 67pt; background-color: rgb(144, 144, 144);" width="90">
</td><td style="border: medium none rgb(212, 208, 200); width: 45pt; background-color: rgb(144, 144, 144);" width="60">
</td><td class="xl27" style="border: 0.5pt solid windowtext; width: 76pt; background-color: rgb(243, 243, 243);" width="101">Check Date</td><td style="border: medium none rgb(212, 208, 200); width: 37pt; background-color: rgb(144, 144, 144);" width="49">
</td></tr><tr style="height: 12pt;" height="16"><td style="border: medium none rgb(212, 208, 200); height: 12pt; background-color: rgb(144, 144, 144);" height="16">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td class="xl28" style="border: medium none rgb(212, 208, 200); background-color: white;" x:num="40709" align="right">Wed 15 Jun 2011</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td></tr><tr style="height: 12pt;" height="16"><td class="xl27" style="border: 0.5pt solid windowtext; width: 19pt; height: 12pt; background-color: rgb(243, 243, 243);" height="16" width="26">Title

</td><td class="xl29" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 48pt; background-color: rgb(243, 243, 243);" width="64">First Name</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 49pt; background-color: rgb(243, 243, 243);" width="66">Last Name</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 36pt; background-color: rgb(243, 243, 243);" width="48">Company</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 44pt; background-color: rgb(243, 243, 243);" width="59">Total</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 46pt; background-color: rgb(243, 243, 243);" width="61">Instalment</td><td class="xl30" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 67pt; background-color: rgb(243, 243, 243);" width="90">Start</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 45pt; background-color: rgb(243, 243, 243);" width="60">Frequency</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 76pt; background-color: rgb(243, 243, 243);" width="101">Last Pmt Due</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 37pt; background-color: rgb(243, 243, 243);" width="49">
</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Jamie</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Smith</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test1</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="7846.64" align="right">$ 7,846.64</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="39932" align="right">Wed 29 Apr 2009</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40709" x:fmla="=IF(G4>I$2, "", CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G4 + 7 * INT((I$2 - G4)/7), G4 + 14 * INT((I$2 - G4)/14), EDATE(G4, DATEDIF(G4, I$2, "m")), EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3)) ))" align="right">Wed 15 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I4<>"", I$2-I4 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Christopher</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Bloggs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test2</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="5830" align="right">$ 5,830.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">50</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="39909" align="right">Mon 06 Apr 2009</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40707" x:fmla="=IF(G5>I$2, "", CHOOSE(MATCH(H5, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G5 + 7 * INT((I$2 - G5)/7), G5 + 14 * INT((I$2 - G5)/14), EDATE(G5, DATEDIF(G5, I$2, "m")), EDATE(G5, 3 * INT(DATEDIF(G5, I$2, "m")/3)) ))" align="right">Mon 13 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I5<>"", I$2-I5 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mrs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Brie</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Masters</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test3</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="28633.439999999999" align="right">$ 28,633.44</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">500</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40429" align="right">Wed 08 Sep 2010</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40702" x:fmla="=IF(G6>I$2, "", CHOOSE(MATCH(H6, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G6 + 7 * INT((I$2 - G6)/7), G6 + 14 * INT((I$2 - G6)/14), EDATE(G6, DATEDIF(G6, I$2, "m")), EDATE(G6, 3 * INT(DATEDIF(G6, I$2, "m")/3)) ))" align="right">Wed 08 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I6<>"", I$2-I6 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mrs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Ben</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Buckingham</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test4</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="800" align="right">$ 800.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">200</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40685" x:fmla="=IF(G7>I$2, "", CHOOSE(MATCH(H7, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G7 + 7 * INT((I$2 - G7)/7), G7 + 14 * INT((I$2 - G7)/14), EDATE(G7, DATEDIF(G7, I$2, "m")), EDATE(G7, 3 * INT(DATEDIF(G7, I$2, "m")/3)) ))" align="right">Sun 22 May 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I7<>"", I$2-I7 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Damien</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Cot</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test5</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="4400" align="right">$ 4,400.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">440</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40685" x:fmla="=IF(G8>I$2, "", CHOOSE(MATCH(H8, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G8 + 7 * INT((I$2 - G8)/7), G8 + 14 * INT((I$2 - G8)/14), EDATE(G8, DATEDIF(G8, I$2, "m")), EDATE(G8, 3 * INT(DATEDIF(G8, I$2, "m")/3)) ))" align="right">Sun 22 May 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I8<>"", I$2-I8 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Peter</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Pesh</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test6</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="17811" align="right">$ 17,811.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40606" align="right">Fri 04 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40704" x:fmla="=IF(G9>I$2, "", CHOOSE(MATCH(H9, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G9 + 7 * INT((I$2 - G9)/7), G9 + 14 * INT((I$2 - G9)/14), EDATE(G9, DATEDIF(G9, I$2, "m")), EDATE(G9, 3 * INT(DATEDIF(G9, I$2, "m")/3)) ))" align="right">Fri 10 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I9<>"", I$2-I9 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Neil</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Smith</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test7</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="1803" align="right">$ 1,803.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">910</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40731" align="right">Thu 07 Jul 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Quarterly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G10>I$2, "", CHOOSE(MATCH(H10, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G10 + 7 * INT((I$2 - G10)/7), G10 + 14 * INT((I$2 - G10)/14), EDATE(G10, DATEDIF(G10, I$2, "m")), EDATE(G10, 3 * INT(DATEDIF(G10, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I10<>"", I$2-I10 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Grant</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Denyer</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test8</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="3960" align="right">$ 3,960.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">330</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40758" align="right">Wed 03 Aug 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G11>I$2, "", CHOOSE(MATCH(H11, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G11 + 7 * INT((I$2 - G11)/7), G11 + 14 * INT((I$2 - G11)/14), EDATE(G11, DATEDIF(G11, I$2, "m")), EDATE(G11, 3 * INT(DATEDIF(G11, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I11<>"", I$2-I11 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Steven</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Gerrard</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test9</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="8400" align="right">$ 8,400.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">700</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40758" align="right">Wed 03 Aug 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G12>I$2, "", CHOOSE(MATCH(H12, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G12 + 7 * INT((I$2 - G12)/7), G12 + 14 * INT((I$2 - G12)/14), EDATE(G12, DATEDIF(G12, I$2, "m")), EDATE(G12, 3 * INT(DATEDIF(G12, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I12<>"", I$2-I12 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Guy</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Seb</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test10</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="4020" align="right">$ 4,020.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Fortnightly

</td><td class="xl33" id="td_post_2644189" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40708" x:fmla="=IF(G13>I$2, "", CHOOSE(MATCH(H13, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G13 + 7 * INT((I$2 - G13)/7), G13 + 14 * INT((I$2 - G13)/14), EDATE(G13, DATEDIF(G13, I$2, "m")), EDATE(G13, 3 * INT(DATEDIF(G13, I$2, "m")/3)) ))" align="right">Tue 14 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I13<>"", I$2-I13 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr></table>

The formula in I4 and down computes the due date of the last payment on or prior to the check date:

=IF(G4>I$2, "",
CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G4 + 7 * INT((I$2 - G4)/7),
G4 + 14 * INT((I$2 - G4)/14),
EDATE(G4, DATEDIF(G4, I$2, "m")),
EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3))
))

The formula in the next column computes whether the row should be highlighted:

=IF(I4<>"", I$2-I4 < 7)

Emmily · Mar 12, 2011

Also, on the 22/03/11 i should not get the red cells as True because that is the day they would have payed so i have to give at least one business for the funds to receive the a/c so the following week 29/03/11 i would expect these cells to be True

Excel Workbook

A

B

C

D

E

F

G

H

I

J

2

22/03/2011

3

Title

First Name

Last Name

Company Name

Total

Instalment

Payment Start Date

Frequency

Last Pmt Due

Check

4

Mr

Jamie

Smith

Test1

7,846.64

100

29/04/2009

Weekly

16-Mar-11

TRUE

5

Mr

Christopher

Bloggs

Test2

5,830.00

50

6/04/2009

Weekly

21-Mar-11

TRUE

6

Mrs

Brie

Masters

Test3

28,633.44

500

8/09/2010

Monthly

8-Mar-11

FALSE

7

Mrs

Ben

Buckingham

Test4

800

200

22/03/2011

Monthly

22-Mar-11

TRUE

8

Mr

Damien

Cot

Test5

4,400.00

440

22/03/2011

Monthly

22-Mar-11

TRUE

9

Mr

Peter

Pesh

Test6

17,811.00

100

4/03/2011

Weekly

18-Mar-11

TRUE

10

Mr

Neil

Smith

Test7

1,803.00

910

7/07/2011

Quarterly

FALSE

11

Mr

Grant

Denyer

Test8

3,960.00

330

8/03/2011

Weekly

22-Mar-11

TRUE

12

Mr

Steven

Gerrard

Test9

8,400.00

700

8/03/2011

Monthly

8-Mar-11

FALSE

13

Mr

Guy

Seb

Test10

4,020.00

100

22/03/2011

Fortnightly

22-Mar-11

TRUE

Sheet2

shg4421 said:
How about a little different approach?

<table style="width: 467pt; border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" width="624"><colgroup><col style="width: 19pt;" width="26"><col style="width: 48pt;" width="64"><col style="width: 49pt;" width="66"><col style="width: 36pt;" width="48"><col style="width: 44pt;" width="59"><col style="width: 46pt;" width="61"><col style="width: 67pt;" width="90"><col style="width: 45pt;" width="60"><col style="width: 76pt;" width="101"><col style="width: 37pt;" width="49"></colgroup><tr style="height: 12pt;" height="16"><td style="border: medium none rgb(212, 208, 200); width: 19pt; height: 12pt; background-color: rgb(144, 144, 144);" height="16" width="26">
</td><td style="border: medium none rgb(212, 208, 200); width: 48pt; background-color: rgb(144, 144, 144);" width="64">
</td><td style="border: medium none rgb(212, 208, 200); width: 49pt; background-color: rgb(144, 144, 144);" width="66">
</td><td style="border: medium none rgb(212, 208, 200); width: 36pt; background-color: rgb(144, 144, 144);" width="48">
</td><td style="border: medium none rgb(212, 208, 200); width: 44pt; background-color: rgb(144, 144, 144);" width="59">
</td><td style="border: medium none rgb(212, 208, 200); width: 46pt; background-color: rgb(144, 144, 144);" width="61">
</td><td style="border: medium none rgb(212, 208, 200); width: 67pt; background-color: rgb(144, 144, 144);" width="90">
</td><td style="border: medium none rgb(212, 208, 200); width: 45pt; background-color: rgb(144, 144, 144);" width="60">
</td><td class="xl27" style="border: 0.5pt solid windowtext; width: 76pt; background-color: rgb(243, 243, 243);" width="101">Check Date</td><td style="border: medium none rgb(212, 208, 200); width: 37pt; background-color: rgb(144, 144, 144);" width="49">
</td></tr><tr style="height: 12pt;" height="16"><td style="border: medium none rgb(212, 208, 200); height: 12pt; background-color: rgb(144, 144, 144);" height="16">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td><td class="xl28" style="border: medium none rgb(212, 208, 200); background-color: white;" x:num="40709" align="right">Wed 15 Jun 2011</td><td style="border: medium none rgb(212, 208, 200); background-color: rgb(144, 144, 144);">
</td></tr><tr style="height: 12pt;" height="16"><td class="xl27" style="border: 0.5pt solid windowtext; width: 19pt; height: 12pt; background-color: rgb(243, 243, 243);" height="16" width="26">Title

</td><td class="xl29" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 48pt; background-color: rgb(243, 243, 243);" width="64">First Name</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 49pt; background-color: rgb(243, 243, 243);" width="66">Last Name</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 36pt; background-color: rgb(243, 243, 243);" width="48">Company</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 44pt; background-color: rgb(243, 243, 243);" width="59">Total</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 46pt; background-color: rgb(243, 243, 243);" width="61">Instalment</td><td class="xl30" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 67pt; background-color: rgb(243, 243, 243);" width="90">Start</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 45pt; background-color: rgb(243, 243, 243);" width="60">Frequency</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 76pt; background-color: rgb(243, 243, 243);" width="101">Last Pmt Due</td><td class="xl27" style="border-right: 0.5pt solid windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext; width: 37pt; background-color: rgb(243, 243, 243);" width="49">
</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Jamie</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Smith</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test1</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="7846.64" align="right">$ 7,846.64</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="39932" align="right">Wed 29 Apr 2009</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40709" x:fmla="=IF(G4>I$2, "", CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G4 + 7 * INT((I$2 - G4)/7), G4 + 14 * INT((I$2 - G4)/14), EDATE(G4, DATEDIF(G4, I$2, "m")), EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3)) ))" align="right">Wed 15 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I4<>"", I$2-I4 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Christopher</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Bloggs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test2</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="5830" align="right">$ 5,830.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">50</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="39909" align="right">Mon 06 Apr 2009</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40707" x:fmla="=IF(G5>I$2, "", CHOOSE(MATCH(H5, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G5 + 7 * INT((I$2 - G5)/7), G5 + 14 * INT((I$2 - G5)/14), EDATE(G5, DATEDIF(G5, I$2, "m")), EDATE(G5, 3 * INT(DATEDIF(G5, I$2, "m")/3)) ))" align="right">Mon 13 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I5<>"", I$2-I5 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mrs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Brie</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Masters</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test3</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="28633.439999999999" align="right">$ 28,633.44</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">500</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40429" align="right">Wed 08 Sep 2010</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40702" x:fmla="=IF(G6>I$2, "", CHOOSE(MATCH(H6, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G6 + 7 * INT((I$2 - G6)/7), G6 + 14 * INT((I$2 - G6)/14), EDATE(G6, DATEDIF(G6, I$2, "m")), EDATE(G6, 3 * INT(DATEDIF(G6, I$2, "m")/3)) ))" align="right">Wed 08 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I6<>"", I$2-I6 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mrs</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Ben</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Buckingham</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test4</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="800" align="right">$ 800.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">200</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40685" x:fmla="=IF(G7>I$2, "", CHOOSE(MATCH(H7, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G7 + 7 * INT((I$2 - G7)/7), G7 + 14 * INT((I$2 - G7)/14), EDATE(G7, DATEDIF(G7, I$2, "m")), EDATE(G7, 3 * INT(DATEDIF(G7, I$2, "m")/3)) ))" align="right">Sun 22 May 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I7<>"", I$2-I7 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Damien</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Cot</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test5</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="4400" align="right">$ 4,400.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">440</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40685" x:fmla="=IF(G8>I$2, "", CHOOSE(MATCH(H8, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G8 + 7 * INT((I$2 - G8)/7), G8 + 14 * INT((I$2 - G8)/14), EDATE(G8, DATEDIF(G8, I$2, "m")), EDATE(G8, 3 * INT(DATEDIF(G8, I$2, "m")/3)) ))" align="right">Sun 22 May 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I8<>"", I$2-I8 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Peter</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Pesh</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test6</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="17811" align="right">$ 17,811.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40606" align="right">Fri 04 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40704" x:fmla="=IF(G9>I$2, "", CHOOSE(MATCH(H9, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G9 + 7 * INT((I$2 - G9)/7), G9 + 14 * INT((I$2 - G9)/14), EDATE(G9, DATEDIF(G9, I$2, "m")), EDATE(G9, 3 * INT(DATEDIF(G9, I$2, "m")/3)) ))" align="right">Fri 10 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I9<>"", I$2-I9 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Neil</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Smith</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test7</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="1803" align="right">$ 1,803.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">910</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40731" align="right">Thu 07 Jul 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Quarterly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G10>I$2, "", CHOOSE(MATCH(H10, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G10 + 7 * INT((I$2 - G10)/7), G10 + 14 * INT((I$2 - G10)/14), EDATE(G10, DATEDIF(G10, I$2, "m")), EDATE(G10, 3 * INT(DATEDIF(G10, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I10<>"", I$2-I10 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Grant</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Denyer</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test8</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="3960" align="right">$ 3,960.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">330</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40758" align="right">Wed 03 Aug 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Weekly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G11>I$2, "", CHOOSE(MATCH(H11, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G11 + 7 * INT((I$2 - G11)/7), G11 + 14 * INT((I$2 - G11)/14), EDATE(G11, DATEDIF(G11, I$2, "m")), EDATE(G11, 3 * INT(DATEDIF(G11, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I11<>"", I$2-I11 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; height: 12pt; background-color: white;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Steven</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Gerrard</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Test9</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="8400" align="right">$ 8,400.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="" align="right">700</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:num="40758" align="right">Wed 03 Aug 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;">Monthly</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:str="" x:fmla="=IF(G12>I$2, "", CHOOSE(MATCH(H12, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G12 + 7 * INT((I$2 - G12)/7), G12 + 14 * INT((I$2 - G12)/14), EDATE(G12, DATEDIF(G12, I$2, "m")), EDATE(G12, 3 * INT(DATEDIF(G12, I$2, "m")/3)) ))">
</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I12<>"", I$2-I12 < 7)" x:bool="FALSE" align="middle">FALSE</td></tr><tr style="height: 12pt;" height="16"><td class="xl31" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; height: 12pt;" height="16">Mr</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Guy</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Seb</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Test10</td><td class="xl32" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="4020" align="right">$ 4,020.00</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="" align="right">100</td><td class="xl33" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40624" align="right">Tue 22 Mar 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;">Fortnightly

</td><td class="xl33" id="td_post_2644189" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background: none repeat scroll 0% 0% yellow;" x:num="40708" x:fmla="=IF(G13>I$2, "", CHOOSE(MATCH(H13, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0), G13 + 7 * INT((I$2 - G13)/7), G13 + 14 * INT((I$2 - G13)/14), EDATE(G13, DATEDIF(G13, I$2, "m")), EDATE(G13, 3 * INT(DATEDIF(G13, I$2, "m")/3)) ))" align="right">Tue 14 Jun 2011</td><td class="xl31" style="border-right: 0.5pt solid windowtext; border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: white;" x:fmla="=IF(I13<>"", I$2-I13 < 7)" x:bool="TRUE" align="middle">TRUE</td></tr></table>

The formula in I4 and down computes the due date of the last payment on or prior to the check date:

=IF(G4>I$2, "",
CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G4 + 7 * INT((I$2 - G4)/7),
G4 + 14 * INT((I$2 - G4)/14),
EDATE(G4, DATEDIF(G4, I$2, "m")),
EDATE(G4, 3 * INT(DATEDIF(G4, I$2, "m")/3))
))

The formula in the next column computes whether the row should be highlighted:

=IF(I4<>"", I$2-I4 < 7)

shg · Mar 12, 2011

So Emmily, my point was to get you to agree on the last payment due date, and then for you to create a formula based on that if the row warrants scrutiny. Can you take a whack at that?

Emmily · Mar 13, 2011

shg4421 said:
So Emmily, my point was to get you to agree on the last payment due date, and then for you to create a formula if the row warrants scrutiny. Can you take a whack at that?

Mate, i tried to have a play with the formula but i could not get it to work.

For 22/03/11 i would not check payment till the 29/03/11.

Its like any bills we pay, if your phone bill is due on 22/03/11, the phone provider is not going to phone you on that day to check whether you have payed, they would wait a week before they start calling you.

Whatever the payment due date according to its frequency I will be checking 1 week later.

shg · Mar 13, 2011

My first question is, do you agree with the last payment due date, based on the start date and payment schedule?

If so, we can move to the "what generates an alert" formula.

Macro help

Emmily

Well-known Member

Emmily

Well-known Member

Excel Facts

shg

MrExcel MVP

Emmily

Well-known Member

Emmily

Well-known Member

shg

MrExcel MVP

Emmily

Well-known Member

Emmily

Well-known Member

shg

MrExcel MVP

Emmily

Well-known Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro help

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock