Date and Save As Questions
 

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!

Date and Save As Questions
 

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

Date and Save As Questions
 

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 and Save As Questions
 

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 and Save As Questions
 

=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

Date and Save As Questions
 

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!

Date and Save As Questions
 

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

Date and Save As Questions
 

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

Date and Save As Questions
 

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

Date and Save As Questions
 

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

Date and Save As Questions
 

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" 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

Date and Save As Questions
 

My bet is that one of those months was mispelled.

"Novmber" is missing an "e"!

<vbg

brentm wrote:

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" 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

Date and Save As Questions
 

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

Date and Save As Questions
 

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