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

  • Thread starter Thread starter StargateFanFromWork
  • Start date Start date
S

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! :oD
 
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! :oD
Click to expand...

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

copy/drag down as far as required.
--ron
 
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.
Click to expand...

=1+eomonth(B2,0)

The EOMONTH function finds the end of the month, and adding 1 day
finds the start of the next month.
 
Ron Rosenfeld said:
B3: =DATE(YEAR($B$2),MONTH($B$2)+ROWS($1:1),1)

copy/drag down as far as required.
--ron
Click to expand...

Thanks, Ron!


JE McGimpsey said:
One way:

=DATE(YEAR(B2),MONTH(B2)+1,1)
Click to expand...


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! :oD
 
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.
 
JE McGimpsey said:
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.
Click to expand...

Oh, yes. I should have mentioned that I understood re the absolute
references in $ symbol. Thanks for figuring out that I meant the
differences in the formula itself and not that part said:
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
Click to expand...

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.
Click to expand...

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! :oD
 
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! :oD
Click to expand...

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
 
Back
Top