Date Manipulation

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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??
 
Upvote 0
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.
 
Upvote 0
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

Aladin
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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 _
Then AddIns("Analysis toolpak").Installed = True
End Sub
 
Upvote 0
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 _
Then AddIns("Analysis toolpak").Installed = True
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top