Could somewhat please tell me how i can find out the last day in th
month using VBA.

I want to assign a variable called ExpiryDate to be the last day in th
month.

Also i would need the variable to be in String format.

Any help appreciated.

Than

--
Message posted from http://www.ExcelForum.com

Hi
try something like the following for getting the last date
of the current month
Expirydate=DateSerial(Year(date),Month(date)+1,1)-1



-----Original Message-----
Could somewhat please tell me how i can find out the last
day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.

Could somewhat please tell me how i can find out the last day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the last day in the
month.

Also i would need the variable to be in String format.

ty this:

Dim ExpiryDate As String
ExpiryDate = CStr(DateSerial(Year(Date), Month(Date) + 1, 0))


--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

Even shorter:
DateSerial(Year(Date), Month(Date) + 1, 0)

The code behind the function actually interprets it as
DateSerial(Year(Date), Month(Date) + 1, 1 - 1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Frank Kabel" wrote in message
...
Hi
try something like the following for getting the last date
of the current month
Expirydate=DateSerial(Year(date),Month(date)+1,1)-1



-----Original Message-----
Could somewhat please tell me how i can find out the last
day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.

The code behind the function actually interprets it as
DateSerial(Year(Date), Month(Date) + 1, 1 - 1)

In most cases that I can think of 1 - 1 would be interpreted as zero. <g

I suspect you meant to say it is interpreted as

DateSerial(Year(Date), Month(Date) + 1, 1) - 1

--
Regards,
Tom Ogilvy


"Rob van Gelder" wrote in message
...
Even shorter:
DateSerial(Year(Date), Month(Date) + 1, 0)

The code behind the function actually interprets it as
DateSerial(Year(Date), Month(Date) + 1, 1 - 1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Frank Kabel" wrote in message
...
Hi
try something like the following for getting the last date
of the current month
Expirydate=DateSerial(Year(date),Month(date)+1,1)-1



-----Original Message-----
Could somewhat please tell me how i can find out the last
day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.

Finds last day of month for the current month. Think you
have to have analysis addin installed.

=EOMONTH(A27,0)

-----Original Message-----
Could somewhat please tell me how i can find out the
last day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.

If you were working with regular formulas, I'd say go to
TOOLS-ADDINS and add the Analysis Toolpack, which contains
the function EOMonth. However, I don't think you'll be
able to use it in VBA. You could try something like
Application.EOMonth(Date,0) and see if it works.
-----Original Message-----
Could somewhat please tell me how i can find out the last
day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.

mm - I guess I wasn't very clear :)

What I meant was DateSerial interprets each argument as an offset from 1

eg.
DateSerial(2004, 1, 1 + 100) is the same as saying 100 days from 1-Jan-2004


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Tom Ogilvy" wrote in message
...
The code behind the function actually interprets it as
DateSerial(Year(Date), Month(Date) + 1, 1 - 1)

In most cases that I can think of 1 - 1 would be interpreted as zero.
<g

I suspect you meant to say it is interpreted as

DateSerial(Year(Date), Month(Date) + 1, 1) - 1

--
Regards,
Tom Ogilvy


"Rob van Gelder" wrote in message
...
Even shorter:
DateSerial(Year(Date), Month(Date) + 1, 0)

The code behind the function actually interprets it as
DateSerial(Year(Date), Month(Date) + 1, 1 - 1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Frank Kabel" wrote in message
...
Hi
try something like the following for getting the last date
of the current month
Expirydate=DateSerial(Year(date),Month(date)+1,1)-1



-----Original Message-----
Could somewhat please tell me how i can find out the last
day in the
month using VBA.

I want to assign a variable called ExpiryDate to be the
last day in the
month.

Also i would need the variable to be in String format.

Any help appreciated.

Thanx


---
Message posted from http://www.ExcelForum.com/

.