Question:
I have a ClarisWorks spreadsheet with a date cell (A2 for this example). I want to fill in a column of payment dates beginning with the 15th day of the month after the date in cell A2 and continuing with the 15th of each subsequent month. How can I do this?
Answer:
There are several formulas that will generate the first payment date. Here are some examples:
Formula 1
= DATE(YEAR(A2+32), MONTH(A2+31),15)
Formula 2
= IF(MONTH(A2)=12,DATE(YEAR(A2)+1,1,15),DATE(YEAR(A2),MONTH(A2)+1,15))
Formula 3
=DATE(IF(MONTH(A2)+1>12,YEAR(A2)+1,YEAR(A2)),IF(MONTH(A2)+1>12,
MONTH(A2)+1-12,MONTH(A2)+1),15)
Formula 4
=DATE(IF(MONTH(A2)=12,YEAR(A2)+1,YEAR(A2)),IF(MONTH(A2)=12,1,MONTH(A2)+1),15)
Let's assume you want to begin the column of payment dates in cell C3. Choose one of the above formulas and enter it for cell C3.
Here's the tricky part. The temptation at this point is to then select Cell C3 and several cells below it and choose "Fill Down" from the Calculate menu. However, you will not get the correct result by doing so.
Instead, click into Cell C4 and retype the formula, changing the cell references from "A2" to Ò"C3".
For example, if you chose to use Formula 1 above, the formula for the second payment date cell should be
= DATE(YEAR(C3+32), MONTH(C3+31),15).
Then select Cell C4 and the number of cells below it that you want filled in with dates and choose "Fill Down" from the Calculate menu.