Showing first day of the next month in a column ...

[StargateFanFromWork]

If I type in May 2007 in B2 (or even more specifically May 1, 2007 or May
25, 2007, etc.), what formula can I put in B3 so that it shows as the first
day of the next month - June 1, 2007. In other words, if I put a month and
year in a cell in column B, that it shows the next month below it as the the
first day.

Example, after inputing either April 2006 or April 10, 2006, etc., in B2
that underneath I'd get this type of list:

May 1, 2006
June 1, 2006
July 1, 2006
August 1, 2006
September 1, 2006
etc.

Thank you once again! D

 

[JE McGimpsey]

One way:

=DATE(YEAR(B2),MONTH(B2)+1,1)

 

[Ron Rosenfeld]

If I type in May 2007 in B2 (or even more specifically May 1, 2007 or May
25, 2007, etc.), what formula can I put in B3 so that it shows as the first
day of the next month - June 1, 2007. In other words, if I put a month and
year in a cell in column B, that it shows the next month below it as the the
first day.

Example, after inputing either April 2006 or April 10, 2006, etc., in B2
that underneath I'd get this type of list:

May 1, 2006
June 1, 2006
July 1, 2006
August 1, 2006
September 1, 2006
etc.

Thank you once again! D

B3: =DATE(YEAR($B$2),MONTH($B$2)+ROWS($1:1),1)

copy/drag down as far as required.
--ron

 

[Stan Brown]

Mon, 28 May 2007 16:31:20 -0400 from StargateFanFromWork

If I type in May 2007 in B2 (or even more specifically May 1, 2007 or May
25, 2007, etc.), what formula can I put in B3 so that it shows as the first
day of the next month - June 1, 2007.

=1+eomonth(B2,0)

The EOMONTH function finds the end of the month, and adding 1 day
finds the start of the next month.

 

[StargateFanFromWork]

B3: =DATE(YEAR($B$2),MONTH($B$2)+ROWS($1:1),1)

copy/drag down as far as required.
--ron

Thanks, Ron!

One way:

=DATE(YEAR(B2),MONTH(B2)+1,1)

Is there any advantage in using one formula over the other? i.e.,


B3: =DATE(YEAR($B$2),MONTH($B$2)+ROWS($1:1),1)
vs.
B3: =DATE(YEAR(B2),MONTH(B2)+1,1)

I tried the =DATE(YEAR(B2),MONTH(B2)+1,1) and that works but was wondering
about the first formula.

Cheers! D

 

[JE McGimpsey]

Both should work fine.

The first always refers to the date in B2, then creates an offset based
on the row that the formula is in. The second creates a relative
reference to the cell above.

The main advantage of the second formula is it's a bit more efficient -
one less function call (don't waste the millisecond or two by
blinking...). A second advantage is that it's slightly easier to
interpret, making it perhaps easier to maintain.

A possible advantage of the first formula is that if you insert a blank
row, the formulas below that row will continue to calculate months based
on B2 (though with a skipped month). This could also be a disadvantage,
depending on your application (i.e., giving a possibly erroneous date
that looks reasonable)

None are especially compelling reasons to pick one over the other, but
my sense of aesthetics would pick the 2nd.

 

[StargateFanFromWork]

Both should work fine.

The first always refers to the date in B2, then creates an offset based
on the row that the formula is in. The second creates a relative
reference to the cell above.

Oh, yes. I should have mentioned that I understood re the absolute
references in $ symbol. Thanks for figuring out that I meant the

The main advantage of the second formula is it's a bit more efficient -
one less function call (don't waste the millisecond or two by
blinking...). A second advantage is that it's slightly easier to

interpret, making it perhaps easier to maintain.

A possible advantage of the first formula is that if you insert a blank
row, the formulas below that row will continue to calculate months based
on B2 (though with a skipped month). This could also be a disadvantage,
depending on your application (i.e., giving a possibly erroneous date
that looks reasonable)

None are especially compelling reasons to pick one over the other, but
my sense of aesthetics would pick the 2nd.

Ah, good enough for me. I may make decisions based on that, too, along with
the consideration of the elegancy of some code over another when they do
pretty much the same thing.

Thanks! D

 

[Ron Rosenfeld]

Is there any advantage in using one formula over the other? i.e.,


B3: =DATE(YEAR($B$2),MONTH($B$2)+ROWS($1:1),1)
vs.
B3: =DATE(YEAR(B2),MONTH(B2)+1,1)

I tried the =DATE(YEAR(B2),MONTH(B2)+1,1) and that works but was wondering
about the first formula.

Cheers! D

Either will work for you application.

Sometimes there are advantages to referring back to the index date, so I've
gotten into that habit.
--ron