# Thread: Excel Date Formula (Excel 97)

1. ## Excel Date Formula (Excel 97)

I need help with an Excel Formula. I have a date which shows 15 Mar 02. The cell underneath it I would like to display 6 months after the 15 Mar 02. The day 15 must stay the same. So Ideally it should display:

15 Mar 02
15 Sep 02
15 Mar 03
15 Sep 03

However, the first cell could have the date 15 Jan 02 and the cells which follows should show the correct dates six monthly.

Any help would be appreciated.

Regards

EnglishRose

2. ## Re: Excel Date Formula (Excel 97)

Let A2 house 15 Mar 02, the initial date.

In A3 enter and copy down:

=EDATE(A2,6)

which requires that the Analysis Toolpak is activated (via Tools|Add-ins)

or

=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))

Aladin

3. ## Re: Excel Date Formula (Excel 97)

Thanks for that it works really well <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

4. ## Re: Excel Date Formula (Excel 97)

Hi englishrose,

What do you want Excel to do if you're source date is the 31st and the target month has 28, 29 or 30 days? Likewise if your starting from 29 or 30 September, which gives 29 February (OK in a leap year) or 30 February? Should these situations be interpreted as falling on the last day of the target month, or rolling over into the next month?

Cheers

5. ## Re: Excel Date Formula (Excel 97)

The source date won't change it will stay at the 15th of each month.

6. ## Re: Excel Date Formula (Excel 97)

OK, but be aware that the solutions you've been given might not give the results you want if you use them in such situations.
Cheers

7. ## Re: Excel Date Formula (Excel 97)

Thanks for the advise

English Rose

#### Posting Permissions

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