1. ## Add 1 Month (Excel XP SR1)

In cell a1, I have a date. It will always be Jan 01 and the year is changeable. Example: A1 might be Jan 01,2002 or it could be Jan 01, 2008.

There needs to be a formula in A2 that will result in Feb 01, (same year as A1).

There needs to be a formula in A3 that will result in Mar 01, (same year as A1)

Etc.

Each formula can reference A1 or the cell directly above it, doesn't matter. The goal is to create a list of 12 dates (the 1st of the month for a complete year), in which the only given is cell A1)

And I have to assume that the ones who use this work will NOT have the Analysis ToolPak installed. That means I can't use the EDATE function.

I hate working with dates.

Just curious here, but is it safer to assume that the end-user WILL have the analysis toolpak installed? The people who will use this worksheet will have Office 97 or later.

I don't remember making a conscience effort to install the Analysis toolpak but it's up and running on my version of Excel.!!

2. ## Re: Add 1 Month (Excel XP SR1)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Tricky

Now knowing exactly what you are up to, I hope this helps:

How about: <font color=red> =DATE(YEAR(\$A\$1),MONTH(\$A\$1)+1,1) </font color=red>

Will this work for you?

The +1 could be also coming from a cell, where you can place the number of months you want to advance.

Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

3. ## Re: Add 1 Month (Excel XP SR1)

Thank you - that formula worked just right. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

4. ## Re: Add 1 Month (Excel XP SR1)

FYI, 2 quick points:
1. Wassim's formula will continue to work if you copied it off farther to the right (i.e., you don't ever have to test if the month parameter evaluates to > 12); and
2. If you ever need month-ending dates, use day zero of the following month. Wassim's formula would change to ...,MONTH()+2,0)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•