Identify last date of a specific month

[Papa Jonah]

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

 

[dan dungan]

Hi Papa,

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

Dan

Newsgroups: microsoft.public.excel.programming
From: "Bob Phillips" <[email protected]>
Date: Thu, 22 Jan 2004 09:43:21 -0000
Local: Thurs, Jan 22 2004 1:43 am
Subject: Re: 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)

message

Jon,

 

[ker_01]

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

 

[ker_01]

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

 

[OssieMac]

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.

 

[Papa Jonah]

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.

 

[Papa Jonah]

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

 

[Rick Rothstein]

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.

 

[OssieMac]

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.