I have a spreadsheet the has in cell G11 a specific month. In G10, I need
the date for the last day of the month. For example, if G11 shows February,
I need G10 to show 2/28/09 or 2/29/08 depending on if it is a leap year. I
would also like the year to be the current year.

My other question is how to have a macro prompt the user to input the file
name & location it is to saved as.

Thanks to all the many gurus out there!

I have a spreadsheet the has in cell G11 a specific month. In G10, I need
the date for the last day of the month. For example, if G11 shows
February,
I need G10 to show 2/28/09 or 2/29/08 depending on if it is a leap year.
I
would also like the year to be the current year.

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

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

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

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

=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,

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

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.

Sorry - cos this is m.p.e.programming I assumed incorrectly you wanted a VB
solution. But Mr Peterson has now sorted you!