Date and Save As Questions

[brentm]

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!

 

[IanKR]

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

 

[IanKR]

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

 

[brentm]

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.

 

[Dave Peterson]

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

 

[IanKR]

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!

 

[brentm]

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]

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(TODAY()))+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]

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]

So did it work?

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.

 

[brentm]

Dave,

You're a freakin genius! It didn't work on one worksheet, but works like a
champ on all the others. I will have to take time to isolate the issue
causing the problem with the one worksheet. thanks for all your time and
help. Have a great weekend!

Brent

 

[Dave Peterson]

My bet is that one of those months was mispelled.

"Novmber" is missing an "e"!

<vbg>

Dave,

You're a freakin genius! It didn't work on one worksheet, but works like a
champ on all the others. I will have to take time to isolate the issue
causing the problem with the one worksheet. thanks for all your time and
help. Have a great weekend!

Brent

 

[brentm]

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]

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.

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.