Thanks:  0
Likes:  0

1. In my first cell I have a date, say
1/1/2001
In the next cell I want the date at the end of the month(I can't use EOmonth):
1/31/2001

VBA has the DateAdd function which is perfect but I can't use it in the spreadsheet. Help!

2. =DATE(YEAR(A1),MONTH(A1)+1,0)

3. On 2002-05-01 13:41, Corticus wrote:
In my first cell I have a date, say
1/1/2001
In the next cell I want the date at the end of the month(I can't use EOmonth):
1/31/2001

VBA has the DateAdd function which is perfect but I can't use it in the spreadsheet. Help!
Why can't you use EOMonth??

4. Try this:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))-1

5. Yes!!

=Date(year(A1),Month(A1)+1),Day(A1))-1

worked perfect,
Thanks for the replies!!
By the way, I couldn't use EoMonth because I send this spreadsheet all over the state and if the analysis toolkit wasn't available on someone's system, it would be a big pain.

6. On 2002-05-02 05:52, Corticus wrote:
Yes!!

=Date(year(A1),Month(A1)+1),Day(A1))-1

worked perfect,
Thanks for the replies!!
By the way, I couldn't use EoMonth because I send this spreadsheet all over the state and if the analysis toolkit wasn't available on someone's system, it would be a big pain.
Are you sure?

Given

2/2/2001 in A1

the above formula computes

3/1/2001

while

=EOMONTH(A1,0)

and

=DATE(YEAR(A1),MONTH(A1)+1,0)

both computes:

2/28/2001

7. Fortunately, I usually start with the first day of the month, but not always. So if anyone knows a way to use EOmonth(which is what I want) without having the Analysis Toolkit installed, that would be great.

8. On 2002-05-02 10:55, Corticus wrote:
Fortunately, I usually start with the first day of the month, but not always. So if anyone knows a way to use EOmonth(which is what I want) without having the Analysis Toolkit installed, that would be great.
Hmmm... Obviously, you don't like:

=DATE(YEAR(A1),MONTH(A1)+1,0)

9. Or you could do the recipient a favor and install it for them (it comes w/ Excel):

Code:
```Private Sub auto_open()
If AddIns("Analysis toolpak").Installed = False _
End Sub```

10. Not that I intended for this question to get so involved but I would like to have the recipients auto_open the Analysis Toolkit, unfortunately the code:

Private Sub auto_open()
If AddIns("Analysis toolpak").Installed = False _
End Sub

gives me a Run-Time 1004 error, Unable to set the installed property of the add-in class.

I can do pretty well with the initial equation provided above, I'm just digging now.

## User Tag List

#### Posting Permissions

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