Dave,

Would it be possible to send you the one spreadsheet that isn't working? I
cannot find any problem with the formatting or the actual data. I keep
getting "#VALUE". Excel doesn't seem to like "1&G11" parts of the formula,
yet when I use a new workbook and copy over the same list and references, it
works just fine.

Thanks again.

"Dave Peterson" wrote:

So did it work?

brentm wrote:

Dave,

G11 is populated from a drop down list (data validated list) that lists the
months in full - January, February, March, etc. So, if the user was wanting
to run the spreadsheet for the month of April, they select "April" from the
drop down list, and G10 should read 4/30/(current year).

Thanks.

"Dave Peterson" wrote:

Maybe...

Do you type in?
December
Dec

If excel can't figure out your string as a month, then you'd be out of luck:

=DATE(YEAR(1&G11&YEAR(TODAY())),MONTH(1&G11&YEAR(T ODAY()))+1,0)

So if you type in Dec in G11, then this portion of the formula:
1&G11&YEAR(TODAY())
evaluates to
1Dec2009
and excel can see that it's a date.

And this portion of the formula
year(today())
is always using the current year.


brentm wrote:

Dave,

Thanks. If G11 only reflects a given month with no day or year information
(it is populated from a drop down list of the 12 months), is there a way for
Excel to know that for December, I want 12/31/(current year) or for February,
I want 2/28(or 29 if a leap year)/(current year)?

Thanks again.

"Dave Peterson" wrote:

=date(year(g11),month(g11)+1,1)-1
or just
=date(year(g11),month(g11)+1,0)

The zeroeth day of the month is the last day of the previous month.

brentm wrote:

Thanks Ian. Since this code is going into a cell, and not in a vba
module/macro, this doesn't seem to work. The formula needs to be in Excel
syntax. I tried =DateSerial(Year(G11), Month(G11) + 1,1) - 1, but that
didn't work either.

"IanKR" wrote:

Go to the first day of the following month and subtract one:

LastDayOfMonth = DateSerial(Year(G11), Month(G11) + 1, 1) - 1

Sorry - the value in cell G11 should be properly syntaxed:

LastDayOfMonth = DateSerial(Year(Range("G11")), Month(Range("G11")) + 1,
1) - 1



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson