Formula to return last day of month for each month in year?

[StargateFan]

A common subject, I've found <g>. I've just spent 40 mins. in the
archives and have learned a lot, but nothing addresses this issue as
my needs are much simpler than the average post. Without the analysis
tookpak and the EOMONTH, is there a way to have cells return the last
day of the month based on the preceding column's month/year?

The trick is that I would like to input just the year in question once
at the top, say in cell A1 and for Excel to populate the 12 months in
the 12 cells below.

I tried doing this in column A underneath, for example:
=A$1 & " - January"
=A$1 & " - February"
=A$1 & " - March"
down to December,

but realized that although this displays perfectly in cells A2 down,
it probably won't be good for the calculation in column B.

So all that's needed is to have a display of each month in column A,
the year being determined by A1 in a way similar in intent to above;
while in column B starting at B2, the formula would return
Tues.Jan.31.2006 for the month of January 2006, Tues.Feb.28.2006,
etc., etc., down the column changing when a different year is put in
A1.

There are many financial transactions that give interest on the last
day of the month (vs. last weekday) and that also pull fees on the
last day. Every time I'm recording this info. I have to pull up the
calendar in the systray where a simple spreadsheet with this info
could be posted above computer station.

Thank you! D

 

[Roger Govier]

Hi

In cell A1 enter 01/01/2006
in A2 =DATE(YEAR(A1),MONTH(A1)+1,1)
Copy down through cells A3:A12
In cell B1
=DATE(YEAR(A1),MONTH(A1)+1,0)
Copy down through cells B2:B12

 

[StargateFan]

Hi

In cell A1 enter 01/01/2006
in A2 =DATE(YEAR(A1),MONTH(A1)+1,1)
Copy down through cells A3:A12
In cell B1
=DATE(YEAR(A1),MONTH(A1)+1,0)
Copy down through cells B2:B12

In trying out your solution, I now know what I was doing wrong before
that was baffling me in trying out the code from the archives. I was
accidentally formatted the day wrong so instead of getting the end of
the month for January 2006, I was getting Tues.Jan.01.2006!! <lol>
That was too funny. I put the month value in for both the month _and_
the day in the formatting. No wonder!

Thanks for this code. It's identical to what I found in the archives,
of course. But I'm still not sold. I want it easy for users to input
and just inputing year and doing so once is about as easy as it gets.
I don't mind the hard work in setting up, but it has to be easy for me
and my users to user forever after <g>.

I've had difficult challenges before have a solution so hoping that
someone knows how to get A1 user cell input of 2006 to then show up as
January 2006 in A2, February 2006 in A3, etc., down to December 2006
(or whatever year is input) where, again, user only has to type in a
year once at the top. And then in the second column, adjacent to each
month/year to have the last day of the month show up, again without
any special tool packs.

Tall order, huh? Here's hoping, though. Thank you so much for your
help. Appreciate it! D

 

[Ron Rosenfeld]

A common subject, I've found <g>. I've just spent 40 mins. in the
archives and have learned a lot, but nothing addresses this issue as
my needs are much simpler than the average post. Without the analysis
tookpak and the EOMONTH, is there a way to have cells return the last
day of the month based on the preceding column's month/year?

The trick is that I would like to input just the year in question once
at the top, say in cell A1 and for Excel to populate the 12 months in
the 12 cells below.

I tried doing this in column A underneath, for example:
=A$1 & " - January"
=A$1 & " - February"
=A$1 & " - March"
down to December,

but realized that although this displays perfectly in cells A2 down,
it probably won't be good for the calculation in column B.

So all that's needed is to have a display of each month in column A,
the year being determined by A1 in a way similar in intent to above;
while in column B starting at B2, the formula would return
Tues.Jan.31.2006 for the month of January 2006, Tues.Feb.28.2006,
etc., etc., down the column changing when a different year is put in
A1.

There are many financial transactions that give interest on the last
day of the month (vs. last weekday) and that also pull fees on the
last day. Every time I'm recording this info. I have to pull up the
calendar in the systray where a simple spreadsheet with this info
could be posted above computer station.

Thank you! D

With just the year in A1, a formula that will give the last day of the month in
A2:A13

A2: =DATE($A$1,ROWS($A$2:A2)+1,0)

copy/drag across to B2 (or in B2 merely put =A2)

To display the dates the way you specify, you can use custom formatting:

Select A2, then Format/Cells/Numbers/Custom Type: mmmm - yyyy
Select B2, then Format/Cells/Numbers/Custom Type: ddd.mmm.dd.yyyy

Finally, select A2:B2 and copy/drag down to A13:B13

One difference from your specification: Formatting cannot give a result for
the day such as 'Tues'; it can only give a three letter day abbreviation 'Tue'
or the full day spelled out. If this is an issue, there is a solution, but it
would certainly be more cumbersome.

If you do not want to custom format the cells, you would have to turn the
results into a text string. Something like:

A2: =TEXT(DATE($A$1,ROWS($A$2:A2)+1,0),"mmmm-yyy")
B2: =TEXT(DATE($A$1,ROWS($A$2:B2)+1,0),"ddd.mmm.dd.yyy")


--ron

 

[StargateFan]

On Mon, 02 Jan 2006 13:19:09 -0500, StargateFan
[snip]

With just the year in A1, a formula that will give the last day of the month in
A2:A13

A2: =DATE($A$1,ROWS($A$2:A2)+1,0)

copy/drag across to B2 (or in B2 merely put =A2)

To display the dates the way you specify, you can use custom formatting:

Select A2, then Format/Cells/Numbers/Custom Type: mmmm - yyyy
Select B2, then Format/Cells/Numbers/Custom Type: ddd.mmm.dd.yyyy

Finally, select A2:B2 and copy/drag down to A13:B13

One difference from your specification: Formatting cannot give a result for
the day such as 'Tues'; it can only give a three letter day abbreviation 'Tue'
or the full day spelled out. If this is an issue, there is a solution, but it
would certainly be more cumbersome.

If you do not want to custom format the cells, you would have to turn the
results into a text string. Something like:

A2: =TEXT(DATE($A$1,ROWS($A$2:A2)+1,0),"mmmm-yyy")
B2: =TEXT(DATE($A$1,ROWS($A$2:B2)+1,0),"ddd.mmm.dd.yyy")

This worked perfectly. (And, no, XL2K regular formatting just fine
<g>.) Thanks! D

 

[Ron Rosenfeld]

This worked perfectly. (And, no, XL2K regular formatting just fine
<g>.) Thanks! D

You're welcome. Glad to help. Thanks for the feedback.
--ron