Date Manipulation

Corticus · May 1st, 2002, 02:41 PM

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!

Aladin Akyurek · May 1st, 2002, 02:43 PM

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

bergy · May 1st, 2002, 02:45 PM

Quote:

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??

Ricky Morris · May 1st, 2002, 02:46 PM

Try this:

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

Corticus · May 2nd, 2002, 06:52 AM

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.

Aladin Akyurek · May 2nd, 2002, 07:09 AM

Quote:

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

Corticus · May 2nd, 2002, 11:55 AM

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.

Aladin Akyurek · May 2nd, 2002, 12:17 PM

Quote:

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)

NateO · May 2nd, 2002, 12:24 PM

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

Corticus · May 2nd, 2002, 12:41 PM

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.

May 1st, 2002, 02:46 PM	#4
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Try this: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))-1 __________________ It's never too late to learn something new. Ricky

May 2nd, 2002, 12:24 PM	#9
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 02:41 PM	#1
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	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!

May 1st, 2002, 02:43 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	=DATE(YEAR(A1),MONTH(A1)+1,0)

May 2nd, 2002, 06:52 AM	#5
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	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.

May 2nd, 2002, 11:55 AM	#7
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	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.

May 2nd, 2002, 12:41 PM	#10
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	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.