Make dates change to next 3 months when payment is paid.

[danjames11]

I have a list of dates when people need to pay on excel and I have a tick box to tick when they have paid. Then I want the date to change to the next payment date (3 months on) and the tick box to untick itself. This seems quite complcated but I need to do this.

eg:
heres the list of dates

<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="190" width="524"><col style="width: 110pt;" width="147"> <tbody><tr style="height: 16.5pt;" height="22"> <td class="xl23" style="height: 16.5pt; width: 110pt;" x:num="39955" align="left" height="22" width="147">22/05/2009 by here there is a tick box to say they have paid
</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39956" height="22">23/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39957" height="22">24/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39958" height="22">25/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39959" height="22">26/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39960" height="22">27/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39961" height="22">28/05/2009</td> </tr> <tr style="height: 16.5pt;" align="left" height="22"> <td class="xl23" style="height: 16.5pt;" x:num="39962" height="22">29/05/2009</td> </tr> </tbody></table>
When i tick on the tick box i want the date to move on to the next 3 months by using the date function like this:
=DATE(YEAR(O14), MONTH(O14)+3, DAY(O14))

and the tick box to untick itslef for this next payment date.

Thanks in advance :D

 

[venkat1926]

what is meant by tick box you mean a check box for each of the dates. Or is there just a tick mark in the cell to the right of the date cell.

instead of three month ahead it would be easier to make plus 90 days. even thought even if you want exactly same date 3 month ahead it can be done In this case if the month is october, november december the year also will change making the formula little more complicated. That is why I suggested just 90 days ahead . think about this.

 

[danjames11]

what is meant by tick box you mean a check box for each of the dates. Or is there just a tick mark in the cell to the right of the date cell.

instead of three month ahead it would be easier to make plus 90 days. even thought even if you want exactly same date 3 month ahead it can be done In this case if the month is october, november december the year also will change making the formula little more complicated. That is why I suggested just 90 days ahead . think about this.

Yes the tickbox is on the right of the date .. Web u tick it I want the date to go 3 months ahead .. And I want the year to change because clients are paying ever year..

 

[venkat1926]

if you sue plus 90 days automatically year also will chane if original data is in October, November or December.

sample sheet is given below (remember in excel the dates are entred as month/date/year . it can be formatted as you like.
run this macro on this sheeet data
is this what you want

sub test()
Dim rng As Range, c As Range
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c.Offset(0, 1)<> "" Then
c = c + 90
c.Offset(0, 1).Clear
End If
Next


End Sub

******** language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>

Book3
	A	B	C	D
1	date	paid
2	9/27/2008	v
3	6/1/2008
4	1/28/2009	v
5	3/30/2009
6	2/10/2009
7	11/17/2008	v
Sheet1

 

[danjames11]

Thanks , Its fine Now Iv'e got around it by sorting it out manually.

Thanks, Dan