No thanks.

Maybe someone else will volunteer to look at your file.

Or you could post the formula that you're using and the values in the cells that
are being used in that formula.


brentm wrote:

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


--

Dave Peterson