I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

Hi Papa,

Does this from the Jan 2004 archive do what you want?

Dan

Newsgroups: microsoft.public.excel.programming
From: "Bob Phillips"
Date: Thu, 22 Jan 2004 09:43:21 -0000
Local: Thurs, Jan 22 2004 1:43 am
Subject: Calculating Last day of month

Jon,

Along the same lines as Rob, but a little shorter

myDate = DateSerial(Year(Date), Month(Date) + 1, 0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in
message
...

Jon,

A cool behaviour of DateSerial is that it accepts values outside the
bounds
of a calendar month/day. This means you can choose month 13, which is
interpreted as month 12 + 1

dtmTemp = Now()
dtmTemp = DateSerial(Year(dtmTemp), Month(dtmTemp) + 1, 1) - 1

Rob

"Jon" wrote in message
...
I am having a problem with calculating the last day of the current month
in excel. If I use the EOMONTH function with parameters NOW() and 0 i.e.
EOMONTH(NOW(), 0) in the cell where I want the date to go everything works
fine. However
I want to put the EOMONTH into a macro at which point the compiler
complain the EOMONTH is not a defined sub or function. Are there any VB
functions that will return the last day of the current month

thank you in advanced

Jon

In VBA or via formula, I think your easiest option will be to find the first
date of the subsequent month, and subtract 1.

Formula Example:
Cell A1 = Dec 2008
Cell B1 =
DATE(ROUND(((YEAR(A1)*12+MONTH(A1)+1)/12),0),MOD((YEAR(A1)*12+MONTH(A1)+1),12),1)-1

Code example:
Sub LastMonthDay()
OrgDate = Sheet1.Range("A1").Value
NextMonthStartDate = DateSerial(Year(OrgDate), Month(OrgDate), 1)

ActualLastDay = NextMonthStartDate - 1
End Sub


HTH,
Keith

"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

Oops, on that VBA it should be
Month(OrgDate) + 1
(I forgot the +1 in the first reply)

"ker_01" wrote:

In VBA or via formula, I think your easiest option will be to find the first
date of the subsequent month, and subtract 1.

Formula Example:
Cell A1 = Dec 2008
Cell B1 =
DATE(ROUND(((YEAR(A1)*12+MONTH(A1)+1)/12),0),MOD((YEAR(A1)*12+MONTH(A1)+1),12),1)-1

Code example:
Sub LastMonthDay()
OrgDate = Sheet1.Range("A1").Value
NextMonthStartDate = DateSerial(Year(OrgDate), Month(OrgDate), 1)

ActualLastDay = NextMonthStartDate - 1
End Sub


HTH,
Keith

"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

Keith,
I tried the worksheet function and have interesting results. For the dates
in Dec 08, it worked well in that it identified 12/31/08 as the last date of
December.
However, for all the other dates, it added a year. For example June 09
dates resulted in 6/30/10.
Maybe I buggered your formula. My dates are in column H and this is how I
substituted to accomodate.
=DATE(ROUND(((YEAR(H3)*12+MONTH(H3)+1)/12),0),MOD((YEAR(H3)*12+MONTH(H3)+1),12),1)-1


"ker_01" wrote:

In VBA or via formula, I think your easiest option will be to find the first
date of the subsequent month, and subtract 1.

Formula Example:
Cell A1 = Dec 2008
Cell B1 =
DATE(ROUND(((YEAR(A1)*12+MONTH(A1)+1)/12),0),MOD((YEAR(A1)*12+MONTH(A1)+1),12),1)-1

Code example:
Sub LastMonthDay()
OrgDate = Sheet1.Range("A1").Value
NextMonthStartDate = DateSerial(Year(OrgDate), Month(OrgDate), 1)

ActualLastDay = NextMonthStartDate - 1
End Sub


HTH,
Keith

"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

Hi Papa,

Assuming that B1 is the header the following is the worksheet function for
last day of month. Look up EOMONTH in help for more info.

=EOMONTH(B1,0)

Using the function on the worksheet, it works irrespective of whether B1 is
formatted as text or as a date formatted as mmmm-yy.

If used in VBA as

WorksheetFunction.EoMonth(Range("B1"), 0)

then it works if B1 is a date formatted as mmmm-yy but if B1 is text, it
cannot evaluate a 2 digit year and 08 becomes the current year. However, it
can evaluate December 2008.

--
Regards,

OssieMac


"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

OssieMac,
I am apparently not getting it. My headers are in row 2. My dates are in
column H.
I have tried =eomonth(h2,0) and I have tried =eomonth(h3,0)
Both have resulted in #Name?
I have tried formatting the cell as a date. I'm not sure what is happening.
There doesn't seem to be any help for the EOMonth function either.

"OssieMac" wrote:

Hi Papa,

Assuming that B1 is the header the following is the worksheet function for
last day of month. Look up EOMONTH in help for more info.

=EOMONTH(B1,0)

Using the function on the worksheet, it works irrespective of whether B1 is
formatted as text or as a date formatted as mmmm-yy.

If used in VBA as

WorksheetFunction.EoMonth(Range("B1"), 0)

then it works if B1 is a date formatted as mmmm-yy but if B1 is text, it
cannot evaluate a 2 digit year and 08 becomes the current year. However, it
can evaluate December 2008.

--
Regards,

OssieMac


"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa

You are getting a #Name? error, not a #VALUE! error? Give this a try
anyway...

The EOMONTH function is part of the Analysis ToolPak add-in... you need to
click Tools/Add-Ins, put a check mark next to Analysis ToolPak and then
click OK. Now, press F9 to force sheet to be recalculated and I think your
errors will go away.

--
Rick (MVP - Excel)


"Papa Jonah" wrote in message
...
OssieMac,
I am apparently not getting it. My headers are in row 2. My dates are in
column H.
I have tried =eomonth(h2,0) and I have tried =eomonth(h3,0)
Both have resulted in #Name?
I have tried formatting the cell as a date. I'm not sure what is
happening.
There doesn't seem to be any help for the EOMonth function either.

"OssieMac" wrote:

Hi Papa,

Assuming that B1 is the header the following is the worksheet function
for
last day of month. Look up EOMONTH in help for more info.

=EOMONTH(B1,0)

Using the function on the worksheet, it works irrespective of whether B1
is
formatted as text or as a date formatted as mmmm-yy.

If used in VBA as

WorksheetFunction.EoMonth(Range("B1"), 0)

then it works if B1 is a date formatted as mmmm-yy but if B1 is text, it
cannot evaluate a 2 digit year and 08 becomes the current year. However,
it
can evaluate December 2008.

--
Regards,

OssieMac


"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format
is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in
that
month. I need to do this in order to calculate the number of days
between
that date and today.
TIA,
Papa

Hi Papa,

Did Rick's suggestion work? It should. I forgot about the function being
part of the Analysis Tool Pak.

If it still doesn't work, what version of Excel are you using? Help back as
far as XL2002 has info on EOMONTH function. I don't suppose you were looking
in Help under the VBA editor. If you have help open with VBA and change to
worksheet then you have to click help again because they are 2 completely
different helps.


--
Regards,

OssieMac


"Papa Jonah" wrote:

OssieMac,
I am apparently not getting it. My headers are in row 2. My dates are in
column H.
I have tried =eomonth(h2,0) and I have tried =eomonth(h3,0)
Both have resulted in #Name?
I have tried formatting the cell as a date. I'm not sure what is happening.
There doesn't seem to be any help for the EOMonth function either.

"OssieMac" wrote:

Hi Papa,

Assuming that B1 is the header the following is the worksheet function for
last day of month. Look up EOMONTH in help for more info.

=EOMONTH(B1,0)

Using the function on the worksheet, it works irrespective of whether B1 is
formatted as text or as a date formatted as mmmm-yy.

If used in VBA as

WorksheetFunction.EoMonth(Range("B1"), 0)

then it works if B1 is a date formatted as mmmm-yy but if B1 is text, it
cannot evaluate a 2 digit year and 08 becomes the current year. However, it
can evaluate December 2008.

--
Regards,

OssieMac


"Papa Jonah" wrote:

I have a spreadsheet that identifies months in a column. The format is:
December-08 (for December 2008).
I need to take that and identify that 12/31/2008 is the last date in that
month. I need to do this in order to calculate the number of days between
that date and today.
TIA,
Papa