Spreadsheet: 205422d1357812465-date-problem-alan-renewal-listing
Sheet: ILP Renewal 2012

 |  | ILP RENEWAL LISTING 2012
 | No. | Customer Name | Policy No. | Policy Date | Due Date | Mode |  | Payment | Premium | Trade In |  | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
 | 1 | ABC 1 | 12345A | 2009-04-14 00:00:00 |  | Yearly |  | D.Payment | 1500
 | 2 | ABC 2 | 12345A | 2009-05-11 00:00:00 |  | Monthly |  | D.Payment | 2400 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 3 | ABC 3 | 12345A | 2009-08-03 00:00:00 |  | Yearly |  | C.Card | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 4 | ABC 4 | 12345A | 2009-10-19 00:00:00 |  | Monthly |  | C.Card | 1500 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | This is using for tracking my customer payment, some more customer is pay by monthly, yearly,half yealy, quarterly. Policy date can not be change, the due date can remind me for the due date is coming, but in here I dun know how to control the due date coz some is yearly, half yearly,quarterly & monthly. here is problem 1.                                                                                                                                                                The second is how can the due date know if the payment has been made and go for the next due date                                                                                                                                                                                          [NAME] O=Paid         Red Box N=Not Paid        Orange box R=Receive
 | 5 | ABC 5 | 12345A | 2009-10-19 00:00:00 |  | Monthly |  | D.Payment | 1920 |  |  | O | O | O | O | O | O | O | O | O | O | O | N
 | 6 | ABC 6 | 12345A | 2009-12-03 00:00:00 |  | Monthly |  | C.Card | 1500 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 7 | ABC 7 | 12345A | 2010-02-24 00:00:00 |  | Yearly |  | D.Payment | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 8 | ABC 8 | 12345A | 2010-02-24 00:00:00 |  | Yearly |  | D.Payment | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 9 | ABC 9 | 12345A | 2010-04-19 00:00:00 |  | Yearly |  | C.Card | 3230 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 10 | ABC 10 | 12345A | 2010-06-21 00:00:00 |  | Monthly |  | C.Card | 2000 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 11 | ABC 11 | 12345A | 2010-09-13 00:00:00 |  | Yearly |  | D.Payment | 1500 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 12 | ABC 12 | 12345A | 2010-10-06 00:00:00 |  | Monthly |  | C.Card | 1550 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 13 | ABC 13 | 12345A | 2010-11-08 00:00:00 |  | Monthly |  | D.Payment | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O
 | 14 | ABC 14 | 12345A | 2010-11-28 00:00:00 |  | Monthly |  | D.Payment | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O
 | 15 | ABC 15 | 12345A | 2011-01-06 00:00:00 |  | Monthly |  | D.Payment | 2700 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 16 | ABC 16 | 12345A | 2011-02-08 00:00:00 |  | Monthly |  | D.Payment | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 17 | ABC 17 | 12345A | 2011-02-28 00:00:00 |  | Monthly |  | C.Card | 3000 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 18 | ABC 18 | 12345A | 2011-02-28 00:00:00 |  | Monthly |  | C.Card | 1950 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 19 | ABC 19 | 12345A | 2011-03-28 00:00:00 |  | Monthly |  | C.Card | 1800 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 20 | ABC 20 | 12345A | 2011-03-28 00:00:00 |  | Monthly |  | C.Card | 2400 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 21 | ABC 21 | 12345A | 2011-03-28 00:00:00 |  | Monthly |  | C.Card | 3000 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 22 | ABC 22 | 12345A | 2011-04-19 00:00:00 |  | Monthly |  | C.Card | 1500 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 23 | ABC 23 | 12345A | 2011-05-16 00:00:00 |  | Monthly |  | D.Payment | 2400 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 24 | ABC 24 | 12345A | 2011-06-20 00:00:00 |  | Monthly |  | C.Card | 2640 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 25 | ABC 25 | 12345A | 2011-07-06 00:00:00 |  | Monthly |  | C.Card | 3000 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 26 | ABC 26 | 12345A | 2011-08-22 00:00:00 |  | Yearly |  | D.Payment | 2220 | 5000 |  | O | O | O | O | O | O | O | N | N | N | N | N
 | 27 | ABC 27 | 12345A | 2011-08-24 00:00:00 |  | Yearly |  | D.Payment | 6620 |  |  | O | O | O | O | O | O | O
 | 28 | ABC 28 | 12345A | 2011-09-26 00:00:00 |  | Monthly |  | C.Card | 1560 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 29 | ABC 29 | 12345A | 2011-09-28 00:00:00 |  | Monthly |  | C.Card | 2400 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 30 | ABC 30 | 12345A | 2011-09-28 00:00:00 |  | Monthly |  | C.Card | 1560 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 31 | ABC 31 | 12345A | 2011-10-28 00:00:00 |  | Yearly |  | C.Card | 2700 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 32 | ABC 32 | 12345A | 2011-11-16 00:00:00 |  | Monthly |  | C.Card | 3000 |  |  | O | O | O | O | O | O | O | O | O | O | O | O
 | 33 | ABC 33 | 12345A | 2012-02-13 00:00:00 |  | Top Up |  | D.Payment | 3600 |  |  | O | O | O | O | O | O | O | O | O | O | O | R
 | 34 | ABC 34 | 12345A | 2012-02-24 00:00:00 |  | Top Up |  | D.Payment | 3000 |  |  | O | O | O | O | O | O | O | O | O | O | O
 | 35 | ABC 35 | 12345A | 2012-04-09 00:00:00 |  | Monthly |  | C.Card | 2400 |  |  |  |  |  | O | O | O | O | O | O | O | O | O
 | 36 | ABC 36 | 12345A | 2012-05-07 00:00:00 |  | Monthly |  | C.Card | 1860 |  |  |  |  |  |  | O | O | O | O | O | O | O | O
 | 37 | ABC 37 | 12345A | 2012-06-07 00:00:00 |  | Yearly |  | C.Card | 1944 |  |  |  |  |  |  |  | O | O | O | O | O | O | O
 | 38 | ABC 38 | 12345A | 2012-06-28 00:00:00 |  | Monthly |  | D.Payment | 2100 |  |  |  |  |  |  |  | O | O | O | O | O | O
 | 39 | ABC 39 | 12345A | 2012-08-09 00:00:00 |  | Monthly |  | D.Payment | 1500 |  |  |  |  |  |  |  |  |  | O | O | O | O
 | 40 | ABC 40 | 12345A | 2012-09-11 00:00:00 |  | Monthly |  | C.Card | 2100 |  |  |  |  |  |  |  |  |  |  | O | O | O | O
 | 41 | ABC 41 | 12345A | 2012-09-13 00:00:00 |  | Monthly |  | D.Payment | 1920 |  |  |  |  |  |  |  |  |  |  | O | O | O
 | 42 | ABC 42 | 12345A | 2012-09-27 00:00:00 |  | Yearly |  | C.Card | 2400 | 3000 |  |  |  |  |  |  |  |  |  | O | O | O | O
 | 43 | ABC 43 | 12345A | 2012-10-08 00:00:00 |  | Yearly |  | C.Card | 2400 | 8000 |  |  |  |  |  |  |  |  |  |  | O | O | O
 | 44 | ABC 44 | 12345A | 2012-10-08 00:00:00 |  | Yearly |  | C.Card | 3000 |  |  |  |  |  |  |  |  |  |  |  | O | O | O
 | 45 | ABC 45 | 12345A | 2012-10-09 00:00:00 |  | Yearly |  | C.Card | 2100 |  |  |  |  |  |  |  |  |  |  |  | O | O | O
 | 46 | ABC 46 | 12345A | 2012-10-24 00:00:00 |  | Monthly |  | C.Card | 1800 |  |  |  |  |  |  |  |  |  |  |  | O | O | O
 | 47 | ABC 47 | 12345A | 2012-10-25 00:00:00 |  | Yearly |  | D.Payment | 2400 |  |  |  |  |  |  |  |  |  |  |  | O | O | O