Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dkline
 
Posts: n/a
Default Date Math Problem

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?



  #2   Report Post  
Dave R.
 
Posts: n/a
Default

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.



"Dkline" wrote in message
...
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?





  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 3 Mar 2005 14:55:38 -0500, "Dkline" wrote:

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(Bas eDate))-
IF(DAY(DATE(YEAR(BaseDate),MONTH(BaseDate)+ROW(),D AY(BaseDate)))
< DAY(BaseDate),DAY(DATE(YEAR(BaseDate),MONTH(BaseDa te)+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 C1:Dn where, in this case, n =
NumMonths =36. Again, that can be varied depending on your requirements.


--ron
  #4   Report Post  
Dkline
 
Posts: n/a
Default

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." wrote in message
...
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.



"Dkline" wrote in message
...
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?







  #5   Report Post  
Dave R.
 
Posts: n/a
Default

=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).




"Dkline" wrote in message
...
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." wrote in message
...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 date formulas problem Andrew Warren Excel Worksheet Functions 4 January 6th 05 12:35 PM
Problem with date base units for x axis Peter Carr Charts and Charting in Excel 1 December 15th 04 10:11 AM
problem with formatting cell to date format Del Excel Worksheet Functions 7 December 8th 04 06:14 PM
Problem with Date format from VBA code twig Excel Discussion (Misc queries) 3 December 7th 04 07:01 PM
In Excel, I need a date math formula... Lisa Excel Discussion (Misc queries) 2 December 3rd 04 01:13 AM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"