Date Math Problem

[Dkline]

I have a workbook with 115 worksheets. I am retrofitting a new date
calculation in each worksheet via a macro.

The purpose of this is to add one month in each row BUT not allow the day to
be greater than the last day of the month. So if the issue date is December
31, a renewal date cannot be February 31, it can be either February 28th or
29th if in a leap year.

This formula starts in Column D in row 20 and, in the example shown below,
is row 31 - the row in which the date should be 1/29/05, not 04 - the start
of a new year.

The Day portion of the below formula takes advantage of the Date function in
which if you enter a 0 for the Day, you get the last day of the previous
month.

=DATE(YEAR(D31+IF(C31=12,1,0)),IF(C31=12,MONTH($D$20),MONTH(D31)+1),MIN(DAY(DATE(YEAR(D31)+IF(MONTH(D31)=12,1,0),IF(MONTH(D31)+1=13,1,MONTH(D31)+1)+1,0)),DAY($D$20)))

The absolute reference of $D$20 is the issue date of an insurance policy.
Column "C" in the above formula counts the month in the the policy year.

Mo Date

1 01/29/04
2 02/29/04
3 03/29/04
4 04/29/04
5 05/29/04
6 06/29/04
7 07/29/04
8 08/29/04
9 09/29/04
10 10/29/04
11 11/29/04
12 12/29/04
1 01/29/04
2 02/29/04


Date in Month 1 is hardcoded.

The basic question is why does the formula work for all but 8 cases out of
the 115 in this workbook? Am I just lucky with the ones on which it does
work?

 

[Dave R.]

A simplified formula would be:

=EDATE($A$1,1)

If you want to use that for a range of dates, try

=EDATE($A$1,ROW(1:1)) and copy down.

 

[Ron Rosenfeld]

I have a workbook with 115 worksheets. I am retrofitting a new date
calculation in each worksheet via a macro.

The purpose of this is to add one month in each row BUT not allow the day to
be greater than the last day of the month. So if the issue date is December
31, a renewal date cannot be February 31, it can be either February 28th or
29th if in a leap year.

This formula starts in Column D in row 20 and, in the example shown below,
is row 31 - the row in which the date should be 1/29/05, not 04 - the start
of a new year.

The Day portion of the below formula takes advantage of the Date function in
which if you enter a 0 for the Day, you get the last day of the previous
month.

=DATE(YEAR(D31+IF(C31=12,1,0)),IF(C31=12,MONTH($D$20),MONTH(D31)+1),MIN(DAY(DATE(YEAR(D31)+IF(MONTH(D31)=12,1,0),IF(MONTH(D31)+1=13,1,MONTH(D31)+1)+1,0)),DAY($D$20)))

The absolute reference of $D$20 is the issue date of an insurance policy.
Column "C" in the above formula counts the month in the the policy year.

Mo Date

1 01/29/04
2 02/29/04
3 03/29/04
4 04/29/04
5 05/29/04
6 06/29/04
7 07/29/04
8 08/29/04
9 09/29/04
10 10/29/04
11 11/29/04
12 12/29/04
1 01/29/04
2 02/29/04


Date in Month 1 is hardcoded.

The basic question is why does the formula work for all but 8 cases out of
the 115 in this workbook? Am I just lucky with the ones on which it does
work?

Starting from some BaseDate, if you have the Analysis Tool Pack installed, you
can use the EDATE function to add one month, and adjust for the end of the
month the way you describe.

If you do not have/want the ATP installed, you can use the general formula:

=DATE(YEAR(BaseDate),MONTH(BaseDate)+ROW(),DAY(BaseDate))-
IF(DAY(DATE(YEAR(BaseDate),MONTH(BaseDate)+ROW(),DAY(BaseDate)))
< DAY(BaseDate),DAY(DATE(YEAR(BaseDate),MONTH(BaseDate)+ROW(),DAY(BaseDate))))

ROW() may need to be adjusted so that the first row that this function is
entered is adjusted to a 1. For example, if this was entered in A15, you would
want to replace ROW() with ROW()-14.

If you are doing this using a VB macro, you can write a VB routine. Such as:

==============================
Sub AddMonths()
Dim BaseDate As Date
Const NumMonths As Long = 36
Dim i As Long
Dim FirstMonth As Range

BaseDate = [A1].Value
Set FirstMonth = Range("C1")

For i = 1 To NumMonths
With FirstMonth(i, FirstMonth.Column)
.Value = i Mod 12
If i Mod 12 = 0 Then .Value = 12
End With
FirstMonth(i, FirstMonth.Column + 1).Value = dateadd("m", i, BaseDate)
Next i

End Sub
=======================

In the above, BaseDate is in A1, but it could be hard coded in the routine, or
entered via an Input Box.

The month number and dates are entered in C1n where, in this case, n =
NumMonths =36. Again, that can be varied depending on your requirements.


--ron

 

[Dkline]

Your answer works but I'm not entirely sure why. How does the Row(1:1) work?
This creates an array of consecutive integers?

 

[Dave R.]

=ROW(1:1), or =ROW(1:65536) for that matter, returns the row number of the
first referenced row (in this case 1). It is similar to other references in
excel in that it can be relative as in ROW(1:1) or absolute as in
ROW($1:$1) - which will not change if copied elsewhere.

Copying ROW(1:1) down just moves the original reference like copying any
other relative reference formula down. Copying down one row will give
ROW(2:2), copying it down 4 rows will give ROW(5:5). Any of these just
returns the row number of the first row, so it can be used to create arrays
of consecutive integers (between 1 and 65536, but you could always add or
subtract from the row number this returns, if you needed integers beyond
this range).